Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Choices in a cell with HLOOKUP
Column A- Is an Item number that you enter manually
Column B - has an H lookup formula that refers to the number entered in column A (Column A is an item number and when entered in Column B the items description comes up) I would like column C to have a drop down menu that only has the color choices that are available for that item. I'm stuck on column C. Can you do an HLOOKUP formula refering to another cell that contains an HLOOKUP formula? Any help would be appreciated. Thank you -- -H |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Choices in a cell with HLOOKUP
Put this code in the sheet module for that sheet
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 1 Then ' code to add a DataValidation list to column C, ' or code to change the DataValidation list in column C End If Application.EnableEvents = True End Sub Record and modify a macro to add or modify a datavalidation and add this code to the above. Remember to have lists created for color variations. Or you can build extensive code to create those lists through code. If you haven't worked with DataValidation - you'll find it under the Data menu. Write back if you need more help. -- steveB Remove "AYN" from email to respond "H" wrote in message ... Column A- Is an Item number that you enter manually Column B - has an H lookup formula that refers to the number entered in column A (Column A is an item number and when entered in Column B the items description comes up) I would like column C to have a drop down menu that only has the color choices that are available for that item. I'm stuck on column C. Can you do an HLOOKUP formula refering to another cell that contains an HLOOKUP formula? Any help would be appreciated. Thank you -- -H |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Choices in a cell with HLOOKUP
Steve,
Thank you so much for your reply. I understand DataValidation but that was about all I understood from the reply. Every item number that would be entered in Column A would have to have it's own DataValidation list that would need to show in Column C. This is because every item number has a different list of colors to choose from. Would I then create this on another sheet and create an HLOOKUP? I'm so confused. I'm feeling stupid but could you please try another way to explain the answer to me. Thank you for you time and patience. -- -H "STEVE BELL" wrote: Put this code in the sheet module for that sheet Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 1 Then ' code to add a DataValidation list to column C, ' or code to change the DataValidation list in column C End If Application.EnableEvents = True End Sub Record and modify a macro to add or modify a datavalidation and add this code to the above. Remember to have lists created for color variations. Or you can build extensive code to create those lists through code. If you haven't worked with DataValidation - you'll find it under the Data menu. Write back if you need more help. -- steveB Remove "AYN" from email to respond "H" wrote in message ... Column A- Is an Item number that you enter manually Column B - has an H lookup formula that refers to the number entered in column A (Column A is an item number and when entered in Column B the items description comes up) I would like column C to have a drop down menu that only has the color choices that are available for that item. I'm stuck on column C. Can you do an HLOOKUP formula refering to another cell that contains an HLOOKUP formula? Any help would be appreciated. Thank you -- -H |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Choices in a cell with HLOOKUP
I like to use Index-match functions. These return the corresponding item
value for that corresponds to the Reference cell. The match function identifies the row # that contains the reference cell value. The reference cell can either be an entered value, or a calculated value. Table on Sheet2 with Item #'s in A, Descriptors in B, and what ever else in C, D, E, & F With item number in A1 In B1 =INDEX(Sheet2!$A$1:$F$25),MATCH(A1,Sheets2!$A:$A,0 ),2) In C1 ==INDEX(Sheet2!$A$1:$F$25),MATCH(B1,Sheets2!$B:$B, 0),3) or you can refer to named ranges, or many different ranges. Just remember to use: all items in [ ] can be an entered value or a calculated value Best to use sheet and range identifiers for [Table Reference], range reference of [Reference Cell], and numbers for [Column.....]. Prepare for this to blow-up when the Reference cell value is not in the table. You can build these in code and have the code determine which table to refer to = Index([Table reference, match([Reference Cell],[Column of table],0),[column for the value you are looking for]) Let me know if this makes any sense to you. -- steveB Remove "AYN" from email to respond "H" wrote in message ... Steve, Thank you so much for your reply. I understand DataValidation but that was about all I understood from the reply. Every item number that would be entered in Column A would have to have it's own DataValidation list that would need to show in Column C. This is because every item number has a different list of colors to choose from. Would I then create this on another sheet and create an HLOOKUP? I'm so confused. I'm feeling stupid but could you please try another way to explain the answer to me. Thank you for you time and patience. -- -H "STEVE BELL" wrote: Put this code in the sheet module for that sheet Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 1 Then ' code to add a DataValidation list to column C, ' or code to change the DataValidation list in column C End If Application.EnableEvents = True End Sub Record and modify a macro to add or modify a datavalidation and add this code to the above. Remember to have lists created for color variations. Or you can build extensive code to create those lists through code. If you haven't worked with DataValidation - you'll find it under the Data menu. Write back if you need more help. -- steveB Remove "AYN" from email to respond "H" wrote in message ... Column A- Is an Item number that you enter manually Column B - has an H lookup formula that refers to the number entered in column A (Column A is an item number and when entered in Column B the items description comes up) I would like column C to have a drop down menu that only has the color choices that are available for that item. I'm stuck on column C. Can you do an HLOOKUP formula refering to another cell that contains an HLOOKUP formula? Any help would be appreciated. Thank you -- -H |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want excel to input data in a cell from multiple cell choices | Excel Worksheet Functions | |||
Using Multiple Choices to Select a Value in a Cell | Excel Discussion (Misc queries) | |||
=if with multiple choices | Excel Worksheet Functions | |||
Vlookup on multiple choices | Excel Discussion (Misc queries) | |||
How do I allow multiple choices in a list-box? | Excel Worksheet Functions |