How to set a cell's content based on a selection from a dropdown?

Question

I have a little spreadsheet I'm creating to track my progress in a game. There are quest chains in the game that earn you points in an event. These are additive when trying to determine your total score in the event. To make data entry cleaner, I have a section with columns of the various levels for each quest chain. They look like the first five columns here:

Quest	Level 1	Level 2	Level 3	Level 4	Level achieved (dropdown)	Points achieved
Apple	25	50	150
Banana	25	50	150
Cantaloupe	25	50	150
Durian	25	100	200
Eggplant	200
Fig	25	50	150
Grape	25	40	100	150	Level 3	165
Honeydew	20	60	150

All of this is on a single sheet - while I understand it may be conventional to put separate calculations on a separate page, I like the convenience of having them all on one sheet so that I can see them all at the same time. That's why I don't think the solution provided in this question is likely helpful to me, as it relates to content on separate sheets. If you'd like to see a screenshot of the actual spreadsheet, here you go.

The other two columns are the ones I'm interested in. I have a Points achieved column where I've been manually adding the points I've earned so far but this is annoying because I have to keep referring back to the game to see what each level is worth since they're inconsistent for each quest chain.

What I'd like to do is have a drop down for the Level achieved, with options for Level 1-4 (I've already done this) and as I play the game, when I complete another level in the chain, I can change the Level achieved dropdown for that quest to the current level. When I do so, this should automatically update the Points achieved to be the total of points from the current and any prior levels.

Example:

I've just completed the Grape quest's third level. As such, I update the Level achieved dropdown to "Level 3" and the Points achieved cell for Grape should now automatically update to 165, which is Levels 1-3 summed (25 + 40 + 100). (This example can be seen in the chart above.)

My problem:

So, I'm not sure what I need to do at this point. I can imagine a few possibilities.

1. Maybe I can make the calculation simpler by creating a sheet that adds up the totals, so I don't need to set Level 2 as equivalent to the value of Level 1 + Level 2.
2. Is it possible to have some sort of magic that says Level n = the sum of Level 1 + ... Level n without making a new sheet?

The end result I want is to be able to choose the level in the dropdown and have the Points achieved cell automatically populate with the value of the levels added together. Either way, the crux of my question is - How do I use the value of a dropdown menu to determine the value of another cell? I'm happy to update any of my dropdown or column names as necessary to make it work but I'd prefer to keep everything in the same sheet if possible since there's not actually a ton of content I'm tracking.

---

Some notes:

While I love spreadsheets, I don't understand the finer magic of them, so if the way I'm using this is unconventional, that's not surprising. I just want to make a useful spreadsheet that I can update easily with data I've already gathered.

I've poked around here on SO and in Google and while I found some possible sources of information, I think I'm struggling to determine whether the solutions they recommend actually address my specific situation sufficiently for me to solve the issue myself.

Answer 1

The final result that you are looking could be achieved with a simple formula, no need to change columns names, extra columns or extra sheets...

... while you keep your spreadsheet simple.

Simple formula
Using the data provided in the question

=SUM(OFFSET(B8:E8, 0, 0, 1, MATCH(F8,{"Level 1","Level 2","Level 3","Level 4"})))

Explanation

• {"Level 1","Level 2","Level 3","Level 4"} is an array having the values of the dropdown in the order that corresponds to the columns from left to right.
• MATCH is a function that finds the position of the value selected in the dropdown, F8, in the above array.
• OFFSET grabs the cells from Column B to the right based on the number returned by MATCH
• SUM sums the values of the cells grabed by OFFSET.

Copy the formula from G8 to G2:G9.

To adapt this to your sheet, add the following formula to D2:

=SUM(OFFSET(I2:L2, 0, 0, 1, MATCH(C2,{"Level 1","Level 2","Level 3","Level 4"})))

then fill down.

NOTES:

• The formula will return #N/A ^{Error Did not find value '' in MATCH evaluation.} if the Level Achieved (dropdown) is empty. To avoid this you could add IFNA setting the second argument as the "default value". If you want to show the cell empty (blank), keep the second argument empty:

  =IFNA(SUM(OFFSET(I2:L2, 0, 0, 1, MATCH(C2,{"Level 1","Level 2","Level 3","Level 4"}))), )
  

• If your spreadsheet uses , (commas) as the decimal separator, then replace the , with ; (semicolons).

• If your spreadsheet becomes complex, i.e. your sheet becomes very large or you add many sheets and many formulas, then you might require another solution. If that is the case, we will require more details.

References

• Using arrays in Google Sheets

Answer 2

you can do:

=SUM(FILTER(B8:E8, B1:E1<=F8))

and the whole column in one go:

=BYROW(F2:INDEX(F:F, MAX(ROW(F:F)*(F:F<>""))), 
 LAMBDA(x, IFNA(SUM(FILTER(OFFSET(x,,-4,,4), B1:E1<=x)))))

F2:INDEX(F:F, MAX(ROW(F:F)*(F:F<>""))) translates to F2:F8 based on: https://stackoverflow.com/a/74281216/5632629

LAMBDA usage explanation can be found here: https://stackoverflow.com/a/74393500/5632629 in "WHY LAMBDA ?" section