Drop Down with hidden values

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
0
Utf
6/7/2010 5:29:29 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
1745 Views

Similar Articles

[PageSpeed] 43

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
0
Dave
6/7/2010 6:53:27 PM
Reply:

Similar Artilces:

#VALUE error add'g formulas
Trying to add two rows of times with the result in one cell, but having trouble. When I try to add the two rows together, I get a “#VALUE” result. The result for each row displays fine in the HOURS WORKED column on an individual/per-row basis –– but I need to display just one result for both rows - in the “Row 2” cell of the “HOURS WORKED” column for that day. The formulas for each row are shown below. Times are written in standard AM and PM format. The IF statement are for controls to display standard (12-hr.) time format, a text option for writing in notes, and so that &...

Drop Down List #9
I am trying to create a drop down list using two worksheets. How would I create this. One sheet would where the final product is and the other would be where the data is being kept. Watch this 5 min video: How to setup a data validation drop down list: http://youtube.com/watch?v=t2OsWJijrOM -- Biff Microsoft Excel MVP "Vincent Kerzman" <Vincent Kerzman@discussions.microsoft.com> wrote in message news:B2DAA48B-0BD2-4932-AE91-B86D55B8D8A6@microsoft.com... >I am trying to create a drop down list using two worksheets. How would I > create this. > > One s...

Table SY02000
In table SY02000 we have records with a RESTYPE value of 23. What is this? I know what 1 ans 2 are but I am stumped about 23. -- Robert J. O'Donnell MBS Consultant US and LATAM Rock Solid Technologies 512.347.9399x109 Hi Robert Resource type 23 is a report. Usually referred to with the constant REPORTTYPE. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer MBS Support - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessaril...

Drag and drop of eMail to own application
Hi everybody, i like to drag and drop an eMail of Outlook ( Express & Xp ) and Thunderbird into my own application ( MFC ). There'in the dropped eMail should be readed and the subject, body text, Recepiant, ... should be displayed into textboxes. I can check the different formats of the Drag-Event or Clipboard data. An Outlook Express mail is stored in plaintext into the clipboard, so it's seriously possible to extract it. But with Outlook XP Mails i see only the name of a .eml file ( the of file which would be saved on the Destop for example ) But how to extract the data? Do...

reference cell value from fixed column with variable row
template wizard wont let me select a form control (combo box) value to associate a field with but provides a reference cell value which gives me the row number in the other workbook of the value. how do i make a valid cell reference from this reference? i.e. col of referenced value is "A", row is variable depending on what value i pick in combo box. how do i make a reference out of "A" plus the value to equal "A5" ? (so that i can associate a field with it using the template wizard) -- bob z ...

drag and drop
Using office XP I cannot drag and drop items from one folder to another in outlook 2002. Any ideas? Thanks ...

drop down calendar #4
In a cell I want to have a drop down calendar of the current month. I downloaded a template of a calendar and tried data valadation but it only displayed the source range. Any ideas? TIA http://www.windowsdevcenter.com/pub/a/windows/2004/04/27/excelhacks.html "Ray A" wrote: > In a cell I want to have a drop down calendar of the current month. I > downloaded a template of a calendar and tried data valadation but it only > displayed the source range. Any ideas? > TIA > Ray See Ron de Bruin's site for this. http://www.rondebruin.nl/calendar.htm Gord D...

drop down data want to choose more than one item for cell
I have a drop down box that i want folks to be able to choose more than one item from the box to go into the cell. Multiselect from a DV dropdown is possible using VBA. See Debra Dalgleish's site for a downloadable sample workbook with event code. http://www.contextures.on.ca/excelfiles.html#DV0017 Note that you can have the selections in an adjacent cell or in the same cell. Gord Dibben MS Excel MVP On Fri, 26 Feb 2010 12:40:08 -0800, FranW <FranW@discussions.microsoft.com> wrote: >I have a drop down box that i want folks to be able to choose more than...

Drop Down List #2
Hi All Can this be done, as I am struggling. I have data in sheet B and want to create a drop down list in sheet A from this data, and I can't make it happen, I can create a drop down list in sheet A from data in sheet A but not from sheet B Could anyone help please Cheers Steve Name the range on the other sheet, and you can refer to it in the data validation dialog box. There are instructions here: http://www.contextures.com/xlDataVal01.html Steve wrote: > I have data in sheet B and want to create a drop down list in sheet A from > this data, and I can't make it h...

Updating values in 2 forms
I have a script starting with the "FindAll" dialog that helps me search thru several sheet in a particular workbook. When I press "Find Next", the value of ActiveCell is updated in an UserForm1.txtLayout text box. Unfortunately, I dont know how to do it in any other workbook without inserting the following code it in all the sheets in the document. Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.txtLayout.Text = ActiveCell.Text End Sub If any idea please let me know!It is very frustrating! Compile succesfully! ...

Counting Values
What formula can I write to say: How many values in A1:A100 are greater than B2+B3? Try this =COUNTIF(A1:A100,">"&(B2+B3)) -- Regards Ron de Bruin http://www.rondebruin.nl "Adam1 Chicago" <Adam1Chicago@discussions.microsoft.com> wrote in message news:1493F672-3279-49C1-893D-289D71F7D55E@microsoft.com... > What formula can I write to say: How many values in A1:A100 are greater > than > B2+B3? Thanks, that worked well. One more question: How many values in A1:A100 are >B2-B3 and <B2+B3? (I tried using AND and the trick you showed m...

Counting cells before/after a maximum value
If I have a list of numbers like the following... 2,5,2,2,2,2,2,2,2,1,2,5,5,6,9,11,8,11,12 .... across a row of cells, is there a way of counting the number of (non-empty) cells to left of the maximum value, and also to the right of it? In the above example, the answer to both should be 9, of course. TIA -- Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) Well, I think the answer would be 9 for both if the number 1 in the middle of your data was actually a mis-type for something like 13 or 14, or if you want to count away from the MINIMUM and n...

Question of drop-down listbox
Hello, Is it possible to have listbox or combobox with Auto HScroll (ES_AUTOHSCROLL) capabilities. Thank You! I don't get it. ES_AUTOHSCROLL is an Edit control style, how would it apply to a listbox or combobox? What are you trying to do? AliR. "Ririko Horvath" <horvathr@securemethods.com> wrote in message news:uHUo%23OyLFHA.3336@TK2MSFTNGP10.phx.gbl... > Hello, > > Is it possible to have listbox or combobox with Auto HScroll > (ES_AUTOHSCROLL) capabilities. > > Thank You! > > I'm soory for not expressing my question properly...

Values dependant on previous values
Hello I need to build a spreadsheet which when a person selects a particular option on one column than options dependant on that selection are given in the next. So for example if car colour blue us selected in column A, then black, beige and blue are options for the interior in column B, but if red colour is selected, then beige, red and green are displayed in then next column. Help I'm new to complex formlas in excel (if this is complex!) Thanks for looking You can use dependent data validation lists. There are instructions here: http://www.contextures.com/xlDataVal02.html...

Lead Field Values not available in Account
I have created some new values in the Industry pick list (i.e. Govt - Federal) for the Lead system, and it all works fine. If I convert the lead into an account, and then look at the new account's Industry pick list, I see "36" instead of Govt - Federal. Is there a way to map those two fields so that the values are always the same, or will I need to create the same values in both the Leads and Accounts section each time a need a new, custom value? Thanks! Adam Hi Adam, You will have to recreate the new values in Accounts as well. Pay attention to the id's that the ...

reporting on drop-down values
We created drop-downs and wish to add the values to a report, but of course the extentionbase tables only show the numbers. Is there a specific table where all the actual text values are stored? Hi use the stringmaps table to get the description of picklist values. I hope it helps. -- Singh http://mscrmshop.blogspot.com MSCRM Consultant Orion Enterprise Sydney "TheWkndr" wrote: > We created drop-downs and wish to add the values to a report, but of course > the extentionbase tables only show the numbers. Is there a specific table > where all the actual text values a...

Returning a Value into Excel from SQL ADO connection
Hi All first post. I am currently having a problem returning a value into a cell in excel 2000 I understand the ADO connection, and have checked my sqlSTR in query analyser. I use the following to dump to a cell. Set rcset = New ADODB.Recordset rcset.Open sqlstr, dbmain, adOpenKeyset, adLockOptimistic Sheets("Sizing").Range("b19").CopyFromRecordset rcset What I have used this to dump the whole record set out, but the sqlstr I am using only has a specific value in it. I am currently getting #value in my cell, and am wondering if the value coming out of the ADO d...

finding largest value from list of strings
I have a range that is populated from a db using MS-QUERY. All fields are formatted from the source as strings. Some columns contain numeric values. How do I find that largest value in a column? If the fields where values, I could use MAX() or LARGE(), but with the fields being strings, these don't work. Thanks - Daron Try this array-entered** formula (change the ranges to suit your needs)... =MAX(IF(ISNUMBER(--A1:A100),--A1:A100,"")) **Commit formula using Ctrl+Shift+Enter, not just Enter by itself -- Rick (MVP - Excel) "Daron" <Daron.Lowell@gmail.com> ...

Drop user or alias before dropping login
I am trying to delete a user, and I get this error: Login is aliased or mapped to a user in one or more databases. Drop the user or alias before dropping the login. Any advice? Hi Caroline, GO to SQL server --> logins and select the user and open check the properties of the login and see what database access the users has. If any uncheck all the database access and drop the user. Also make sure from the GP front end you have unchecked the company access too. If still you get errors go to each database on the server and make sure they are not in the users list in the databases. ...

leftmost number value
Hi! how can i make this formula to return the leftmost number only? this is an array formula =INDEX(A6:AA6,,MIN(IF(A6:AA6<>"",COLUMN(A6:AA6)))) Thanks! Try this array formula** : =INDEX(A6:AA6,MATCH(TRUE,ISNUMBER(A6:AA6),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "sandy" <sandy@discussions.microsoft.com> wrote in message news:B055531C-451A-4E9C-AF2E-081869306024@microsoft.com... ...

Drop down list 04-23-10
How do you display drop down list button even if the cell is not selected? You can't. Perhaps you've seen the button created from a AutoFilter ot PivotTable? -- Best Regards, Luke M "DLB" <DLB@discussions.microsoft.com> wrote in message news:F248BA68-6874-4712-876D-CF2890DD0BF1@microsoft.com... > How do you display drop down list button even if the cell is not selected? Use a combo box from the Forms toolbar. The drop down arrow is always visible. -- Biff Microsoft Excel MVP "DLB" <DLB@discussions.microsoft.com> wro...

Outlook 2003 Drap and Drop Issue
Hi If you take an email from Outlook and perform a drag and drop to the tasks, the task is created but the message body text is empty. Any reason why this should happen. Cheers ...

re: updating values
that works, but i'll need to add a lot of hidden feilds (20+/-)... Is there another way (perhaps more efficient -if not as simple?) ("there's more than one way to skin a cat") thanks inadvance, mark --------------------------------------------------------------------- "Daryl S" <DarylS@discussions.microsoft.com> wrote in message news:79CFD708-34B3-419A-A3F1-CF7050ACDE9F@microsoft.com... > Mark - > > Add the field [PresetOption] to the form. You can set the .visible > property > to FALSE so the user won't see it. Then the code...

Odd Drop Down Box Behavior
I have a drop down box on a spreadsheet in a workbook with several spreadsheets. For some reason the contents of the drop down box is beeing bled through to other spreadsheets in the exact same cell reference that the drop down box is linked to, replacing what may be in that cell. It happens randomly yet often, and I have not been able to discern any patterrn or catalyst. The contents and cell formatting are being transfered. It also happens on other spreadsheets using the drop down box. This is an Active-X box, not a forms box, using Excel 2007. Anybody seen this? Squeaky...

Drop-Down list in a cell #2
Hi, I have cells in an Excel form which are used to enter data. But it' always same list of choices. I wonder if there is a way to create drop-down list of choice in a cell, so that the user can choose betwee the available choices. Is it possible? Thx! Werne -- Werne ----------------------------------------------------------------------- Werner's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2430 View this thread: http://www.excelforum.com/showthread.php?threadid=39053 Try Debra Dalgleish's page at: http://www.contextures.com/xlDataVal13.html -- Rg...