Multiple Choices in a cell with HLOOKUP

[Guest]

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

 

[STEVE BELL]

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.

 

[Guest]

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.

 

[STEVE BELL]

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.