combo box, index formula problems

[mark67]

Can this be done?

I am using 2 combo boxes to return a value in a 6 x 29 data table (I am
not sure whether to call it an array as I have not defined it as such).
I am using the index function in the target cell to return the proper
value based on the 2 combo box selections.

=INDEX('list prices'!A2:G31,'Info & Spec sheet'!G3,'Info & Spec
sheet'!H3)
This works great for me.

What I am trying to do now is to add another combo box that will have
multiple (3)products in it and depending on the product selected will
revert to the proper 'list price' list . The 'Info & Spec sheet' combo
box selections will not change and that will still pick the proper
price from whichever 6 x 29 data table is selected with the new added
combo box.

does this make sense?

I have tried to modify my index formula line as follows

=INDEX('list prices'!A*(2*H43)*:G*(31*H43)*,'Info & Spec
sheet'!G3,'Info & Spec sheet'!H3)

where H43 would be the cell link number(pointer?) for the new combo
box. In other words I want to increase the value of the number after
"A" &"G" by multiplying the cell number by the value of H43.



any help would be appreciated.

 

[Dick Kusleika]

Mark

One way to do this is to set up three named ranges corresponding to the
three products in your combobox. Then use the value of your combobox in the
INDEX function. If your combobox links to I1, your Index would look like
this

=INDEX(INDIRECT(I1),'Info ...

When you select Product1 from the combobox, then I1 = Product1 and the Index
function refers to the named range Product1.