2

I am super new to anything coding and google sheets. All of what I've done was was trying to follow the directions of another question I saw on this site. However, as expected, nothing is working!

Here's the deal, I want to assign values to certain drop down items, then,the value of the drop down, along with other drop downs, would be added to a separate cell.. However whenever I try to assign values items in a drop down, I keep getting errors. Below is an attachment of what happens. example

So to start off, I have the list. I input the formula as I saw it on another thread. IF(cell="item",value,0). Whether I list one item in the formula, or all, I keep getting the error of "Invalid, Input must be an item on specified list." The Cell automatically turns to a 0 after I input what I believe to be the write formula/function for this to work.

So I come here to ask anyone, to help me figure out what I am doing wrong.

Improve this question
2
  • 1
    Hello @steph, can you edit your question and add some data sample (like your formula) or a dummy sheet perhaps ? thanks.
    – pjmg
    Oct 12, 2019 at 13:11
  • 1
    Also add a link to the question from where you taken the formula.
    – Rubén ♦
    Oct 12, 2019 at 17:06

1 Answer 1

Reset to default
1

You are trying to associate a "text value " in a data validation cell with a given numerical value. One supposes that this is so that you can use the numerical value in another formula. It is common practice in forms for each select item to have a description AND a separate "value", unfortunately Google Sheets does not offer this.

The cell that you use for the "IF" formula can NOT be a data validation cell. The data validation cell takes its values ONLY from the reference list/range, and this explains your error message "Invalid, Input must be an item on specified list.".

However, there are a two alternatives to your complex nested IF formula

1) IFS - "Evaluates multiple conditions and returns a value that corresponds to the first true condition."Doc

=ifs(B1="---",0,B1="Below avg.",100,B1="Average",200,B1="Above avg.",400,B1="High Tier",600,len(B1)=0,0)

OR alternatively

=iferror(ifs(B1="---",0,B1="Below avg.",100,B1="Average",200,B1="Above avg.",400,B1="High Tier",600),0)

This formula is typed in anywhere on the sheet; it evaluates the value of cell B1 (the data validation dropdown), and returns a numerical value. I've also considered the scenario where the validation cell hasn't yet been used.

2) VLOOKUP - Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found. Doc

=iferror(vlookup(B3,List!$A$2:$B$6,2,false),0)

Like theIFS formula, you use this formula in any cell on the sheet.
I created a helper column beside the data validation list; the formula looks up the list value, and returns the value from the adjacent helper column.

This is my preferred approach - it is much efficient that either the nested IF or even the IFS formula since it enables you to easily modify the values without having to edit the formula.

Data Validation cells

Data Validation cells

VLOOKUP Helper

VLOOKUP Helper

Improve this answer
3
  • OH YES!! I believe I finally understand! The value will appear on another cell, and that other cell is where I need to put the formula :O
    – steph
    Oct 13, 2019 at 3:13
  • Do you know a formula, for how I may be able to grab a percentage, from the value of the item lists? For example: In the drop down menu, someone selects Average, which is 200. In another drop down, someone selects "advanced." Advanced would equal 80% of below avg./average/etc. The value of the 80%(160), and the value of Average (200), would be totaled to another cell.
    – steph
    Oct 13, 2019 at 3:20
  • The range of options depends on your model and your needs but the most obvious choices are: 1) use three cells, two x VLOOKUP cells (one for each dropdown) and 1 x multiplication of the two VLOOKUP cells (something like =C3*C5); or 2) use a single cell, create a formula in a single cell in which you combine the two VLOOKUP formula (something like =iferror(vlookup(B3,List!$A$2:$B$6,2,false)*vlookup(B5,List!$D$2:$E$6,2,false),0).
    – Tedinoz
    Oct 13, 2019 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.