Prev: Worksheet Cell Reference
Next: Trouble with dynamic named range
From: woodse on 7 Jun 2010 13:29
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?
--
woodse
From: Dave Peterson on 7 Jun 2010 14:53
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



woodse wrote:
>
> 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?
> --
> woodse

--

Dave Peterson
 | 
Pages: 1
Prev: Worksheet Cell Reference
Next: Trouble with dynamic named range