Drop Down with hidden values

  • Thread starter Thread starter woodse
  • Start date Start date
W

woodse

I need some assistance please. I have a spread sheet that I have drop down
information on. When the user selects the word from the drop down list, I
would like for the word to have a numerical value to it that the user does
not see, but accumulates at the end of the column. Such as:
Hamburger = 25
hotdog = 10
coke = 10 etc.
Sub Total = 45

Any ideas?
 
I would use a table on a (hidden) sheet.

Column A would hold the food item and column B would hold the number.

Then I'd use formulas in the adjacent (also hidden) column that returned the
value for that food item.

=if(a1="","",vlookup(a1,sheet2!a:b,2,false))

And then I could use this kind of formula:
=sum(b:b)
at the bottom of my data in column A.

======
Personally, I wouldn't bother hiding that helper column. I think it makes it
easier for the typical user to understand what's happening.

I may lock those cells with the formulas and protect the worksheet so that the
users can't change my formulas, though.

======
And Debra Dalgleish shares some info on how to use a list (column A of that
hidden sheet) as the list range in the data|validation cells.
http://contextures.com/xlDataVal01.html#Name
 
Back
Top