Search code examples
google-sheets

Google sheets: Create function that calculates depending on dropdown menu text

I am trying to create google sheet that calculates value depending on drop down text menu field selection.

I have a drop down menu with bunch of options:

  • Untrained
  • Improvisation
  • Trained
  • Expert
  • Master
  • Legendary

And when I select one of given options, it would calculate different value dependent on the text selected.

First I tried IF option but it only allows up to 3 variables. Next up I tried ARRAYFORMULA but I couldn't figure it out on how to use it correctly.

EDIT: Picture below of the table with bit more explanation which would hopefully clarify the problem.

  • So when I select Master from the drop down menu, it would automatically calculate value in selected cell C18.
  • In this case it would be 31 =SUM($D$12+F18+G18+$C$7+6)

If I can get just starting point on how to create one of those, I can then expand it on others on my own. Any help would be appreciated on how to implement this.

enter image description here

UPDATE 2: Tedinoz answer was really good and very close to what I was after. Unfortunately it appears to be way too complex and just far enough from my intent for me to really understand enough to finetune and edit it to fill my goal.

I changed the sheet for editable for anyone with link, if it helps: https://docs.google.com/spreadsheets/d/1x2koOHFTSrc1BOvm0tXQ5NFV0_Z8ygohTP9zrLjew1A/edit?usp=sharing

Now what is the problem in suggested solution:

  • It appears to always use dexterity modifier instead of specified modifier and I don't understand solution given enough to correct it myself.
  • All the saves displays just 0 and I don't know why.
    • How difficult would it be to expand same for all cells next to drop down fields?
    • Ideally it would be easy to use character sheet where one just fills few basic stuff and then the sheet would calculate rest.
Solution

  • Skills - Cell D18 =arrayformula(iferror(($C$7*(vlookup(D18:D37,Sheet2!$A$2:$C$7,3,0)))+(vlookup(D18:D37,Sheet2!$A$2:$C$7,2,0)),)+IFERROR(vlookup(vlookup(E18:E37,Sheet2!$E$2:$F$7,2,0),$B$11:$D$16,3,0),)+(F18:F37+G18:G37))

    Saves - Cell J16 =arrayformula(iferror(($C$7*(vlookup(K16:K18,Sheet2!$A$2:$C$7,3,0)))+(vlookup(K16:K18,Sheet2!$A$2:$C$7,2,0)),)+IFERROR(vlookup(vlookup(L16:L18,Sheet2!$E$2:$F$7,2,0),$B$11:$D$16,3,0),)+(M16:M18))

    The formula has several parts:

    • ARRAYFORMULA - create values for all the rows within a section
    • IFERROR - to ensure that a blank in a "Proficiency" or a "stat" cell does not generate #N/A result
    • CALCULATION

      • Sum of the following:

      • Proficiency:

        • Level: $C$7 by
        • Level Multiplier: vlookup(D18:D37,Sheet2!$A$2:$C$7,3,0) . This ensures that Untrained and Improvision score zero for the Level. The multiplier for the other levels = "1", so they score the value of the level for that sheet.

      • Attribute modifier: vlookup(vlookup(E18:E37,Sheet2!$E$2:$F$7,2,0),$B$11:$D$16,3,0) - a nested VLOOKUP searches the Stat value in a range on Sheet2 and returns the full "Attribute" name; then searches the "Attribute" name in the current sheet and returns the "mod" value.

      • Miscellaneous: +(+F18:F37+G18:G37) adds any relevant miscellaneous values.

    Sheet1

    SHEETMARK3

    Validation

    validation

    Assumptions - Sheet2

    ASSUMPTIONSMARK3

    Updated 28 Aug 2023 : This answer has been updated:

    • to correct an earlier solution that used a fixed value for Level instead of the specific value of Level on each sheet.
    • to correct the formula to return the Attribute score.