Using the indirect function with combo boxes

[excelgreen]

Hello Again!

I thought I was good to go with the feedback I had yesterday on this. Here is the link to the last thread. http://www.excelforum.com/excel-work...ml#post2513652

I totally understand how to make the combo box under form controls now but I am not having any success with the indirect function I was using as a list now that I have a combo box. I have attached the current form I am working on that just shows the list function still. Can anyone help me convert this over to combo boxes with the indirect function?

I attached a second form that a gentleman did for me yesterday with the feature I am asking about. It is just lacking the third list that I now have in place. (on the 1st attachment)

THANKS AGAIN!!!!

[contaminated]

Can you point exactly where do u need to use INDIRECT?

[excelgreen]

You bet. The first cell is H17, then g19 and h19. Is that what you were looking for?

[contaminated]

You can't use INDIRECT with comboboxes. Whereas Data Validation List returns a string, which you can use further for INDIRECT. As you know, INDIRECT requires string for its argument. Combobox, instead, returns integer. You must link combo box with an any cell in the sheet, then you will have to use another formulas like INDEX, OFFSET, MATCH and many others to achieve you goal.

[contaminated]

Think this is what you're looking for, See wb..

PS: Your Combo Boxes will show blanks, which are actually can also be removed by adding another formula into OFFSET function. Notice that for the 3rd Drop Down, into OFFSET I used 5 as RowHeigh. Because it's constant it wouldn't change automatically. You will need to use another nested formulas to get rid off blanks in you drop downs.

[excelgreen]

Thanks Again! I really appreciate your time and insight. This looks much better and it is almost complete. H17 and G19 work perfectly but when you have "Int'lAIR" in cell G19 it does not populate any options in cell H19. Everything would be perfecto if we could get that part to functions. Thanks!

[excelgreen]

I figured it out. One of the cell groups had the wrong name attached. After I matched it up, everything works fine. Thanks again!

[grapouitte]

Think this is what you're looking for, See wb..

This post is old, it is closed, but still deserves a thumbs up! Thanks for the example, saved me loads of work!

[lax2734]

how would you then get cells R24, R25, R26 which are continuously changing numbers, to give you the data you need? so say domestic is selected, it is easy enough to make that 1 in r24 become the word domestic with the index function in another cell. but the issue i run into is how would you get r25 to say domesticair or ocean, depending on the choice made before. now the r24,25,26 of course will not have the words, but they have the values and i just want a set of cells to reference to those values and give me the correct words... is this do-able?