google sheets
Using Google Sheets, I have a dropdown list of different actions, and each action has a different score associated with it.
Eg. Action a = 5 points, Action b = 7 points etc
How I can make Google Sheets automatically populate in column B the points associated with the action selected from the dropdown menu in column A?
You might try, in B21, C21 and D21 in order:
=vlookup(vlookup($A21;$A$13:$C$16;column();0);$A$4:$D$6;column();0)+vlookup(vlookup($A21;$A$13:$C$16;column()+1;0);$F$4:$I$6;column();0)
=vlookup(vlookup($a21;$a$13:$c$16;column()-1;0);$a$4:$d$6;column();0)+vlookup(vlookup($a21;$a$13:$c$16;column();0);$f$4:$i$6;column();0)
=vlookup(vlookup($a21;$a$13:$c$16;column()-2;0);$a$4:$d$6;column();0)+vlookup(vlookup($a21;$a$13:$c$16;column()-1;0);$f$4:$i$6;column();0
This is complexity purely resulting from the layout you chose.
I'm not sure I understood your question... But here is one possible answer:
You can apply the formula COUNTIF to the next column like this:
The beginning of the interval will be fixed and the end will be increasing, while you push the formula down to new cells.
So, the fist time a date appears, the content of the cell will be "1". You can then format conditionally the cell for a specific format when it is "1".
If you don't like to see numbers, you can apply the function IF to get a more user friendly look to your file and adapt the conditional format accordingly :
Does this help?
Best Answer
Nested IF statements
The formula
returns the value according to the content of cell A1.
Lookup table
Somewhere on the sheet, say in columns G:H (or on another sheet) make a table:
Then the formula
will return the value from column H that corresponds to the contents of A1. This approach is more extendable: when more actions are added, one only needs to expand the table in G:H. Also, column G can be used by the dropdown in A1 (use data validation for values in a range), so that the list of option is kept in one place.