We have a sheet in which we write the names, each time we enter "Anna" in any field, the value 5 will always appear in the field on the right, or else - if we multiply the field with the word "Anna" by the field with the value 3, then in the third I would like to see the result = 15. Going further, entering the undefined string should not cause an error or automatically assign it a zero value
Hello, great idea, simple solution, could you extend the list of conditions in this way, I don't know the syntax, but I combine something like "= If (A1 =" Anna "; 5; 0) If (A1 =" zosia "; 6; 0 ) If (A1 = "Sylwia"; 7,0) "and such conditions with the first names 35. I went through a lot of possibilities and I did not find an acceptable record, if someone has something to write on this topic, please help
Sub oblicz()
Dim ile As Integer
Dim wiersz As Integer
ile = Cells(1, 1)
For wiersz = 2 To ile + 1
If Cells(wiersz, 1) = "Anna" Then Cells(wiersz, 2) = 1
If Cells(wiersz, 1) = "Sylwia" Then Cells(wiersz, 2) = 2
Next wiersz
I put it in the file in place of Basic, but I do not know how to bind it to trigger the action, if you still have a little patience then look at the file and tell me how to bind it
Nothing came out, I will read and look for a solution in Excel-basic, I have the impression that I know very little about it, your hints are too general for me, it is possible that it cannot be clearer. If anyone can relate more accurately, I would like to read it and apply it
Nothing came out, I will read and look for a solution in Excel-basic, I have the impression that I know very little about it, your hints are too general for me, it is possible that it cannot be clearer. If anyone can relate more accurately, I would like to read it and apply it
Sub oblicz()
Dim ile As Byte
Dim wiersz As Byte
ile = Cells(1, 1)
For wiersz = 2 To ile + 2
If Cells(wiersz, 1).Value = "Anna" Then Cells(wiersz, 2).Value = 1
If Cells(wiersz, 1).Value = "Sylwia" Then Cells(wiersz, 2).Value = 2
If Cells(wiersz, 1).Value = "Iwona" Then Cells(wiersz, 2).Value = 3
Next wiersz
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Call oblicz
End Sub
Or maybe / without a macro / so, through the table in the table, define two rows in the first name in the second value and here search for vertical and retrieve data from that table.
Example: = VLOOKUP (A1, first names, 2, FALSE)
where: a1 - first name entered, possibly selected from the list, first names - an array of two lines, the first name, the second assigned values to the first name, 2 - second row in the table of names FALSE - so that it doesn't take the closest like one :)
naturally, if there is no appearing name, further conditions can be given, e.g. whether it is a mistake or other. The problem can be solved in several ways.
Regards
// sorry I did not read that the above answer was sorry for the duplication
Using the = VLOOKUP function does not work, because the values appear in the input field of the formula, I need the value to appear in the adjacent field. It is also possible that I can't use the formula = vertically search, maybe this function has other syntax possibilities that will move the value to the adjacent column?
I made a correct formula based on the -wicy- hint, the syntax is quite complex with many conditions to be checked. A working example is shown with a blue background, see the example and write if it is possible to modify the code - wicy- or the formulas -kernel-panic- and -adamas_nt- to make it work like in the anna.xls example
- the other solution from visual-basik seems appropriate, but I still don't know where to put this part of "Private Sub Worksheet_SelectionChange (ByVal Target As Excel.Range) Call calculate End Sub " to call the compute procedure each time the focus is transferred from cell to cell
hmm, I don't know if I understand correctly but: in B17 you can enter = VLOOKUP (D17, $ E $ 1: $ G $ 7, 2, FALSE) and copy it down
You can define an array instead of $ E $ 1: $ G $ 7, e.g. the name is the easiest way to select the area and in the place where it shows you the cell number, you click and enter the name (or insert - name - define)
---
To get rid of the error, you can use the function or error (maybe otherwise) and the function if true, it returns a value if the error is, for example, the string I do not have such a name
// with a defined table, in cell b17 enter and copy down
= IF ((ISERROR (VLOOKUP (D17, name; 2, FALSE))) = TRUE, "no name", VLOOKUP (D17, name, 2, FALSE))
instead of the first name you can give this range $ E $ 1: $ G $ 7
But I do not know why these are names that do not appear in that table, maybe I do not understand the example a bit, you have Anna, Zosia, Dorota, Sylwia listed in E14 and only one of these names appears in the table, i.e. Dorota,
kernel-panic - now that you explained how to use the functions, everything is clear and works as intended. The names in the example on a greenish background were not supposed to have anything to do with the example on a blue background, it is just the randomness of using such names. See an example, it works fine
wicy- I stubbornly entered the code you entered as a macro and then it did not work, I laughed at myself, how easy it was as I entered it where necessary (in place of the basic code at sheet1), it recalculates the sheet when changing the focus. See an example, it works fine
I put files with examples in the post, maybe someone will still need it. All three examples work, or rather give a very similar result, but are realized in a completely different method.
AnnaBasic.xls --- is an example using Visual Basic code If.xls --- is an example using a condition check function VLOOKUP.xls --- is an example using VLOOKUP
The discussion revolves around automating value assignment in Excel based on specific names entered in a spreadsheet. The main requirement is to have the value 5 automatically populate in an adjacent cell when "Anna" is entered, and to calculate a result of 15 when "Anna" is multiplied by 3. Users suggest various solutions, including using the IF function for multiple conditions, nesting IF statements, and employing Visual Basic for Applications (VBA) macros for more complex scenarios. Alternatives like VLOOKUP and error handling functions are also discussed to manage undefined strings without causing errors. The conversation highlights the need for clear syntax and practical examples to implement these solutions effectively. Summary generated by the language model.
