fill list based on 2 selections

[leem]

I could use your expert knowledge in figuring out how to populate a column of information.

Basic idea: I have 6 tabs of different information - same set up on each tab. On the data tab I would like column b2 to populate depending on the selection from the drop down boxes. In my example I selected AUG for 1 drop down and CL FT for the other drop down. I would like the numbers from the CL FT tab - AUG column to populate to the data tab b2 and down. I have attached an example for a much better explanation.

Thanks in advance for your help.
Michelle

[tigeravatar]

Michelle,

Attached is a modified version of your posted workbook.
In cell A2 and copied down to A50 is this formula:

Please Login or Register  to view this content.

Then in cell B2 and copied down to B50 is this formula:

Please Login or Register  to view this content.

[jeversf]

two list fill down.xls

Hi Michelle,

How about this?

[tigeravatar]

@jeversf, You have hardcoded into the formula to always use the 'CL FT' sheet regardless of what is selected in the second listbox. You can use the Indirect() function to get around that limitation.

[jeversf]

thanks for that, i haven't used the indirect much before but i can definitely see the benefit to it.
good job on the solution!

[leem]

This is perfect! Thank you so much for your brilliance. And thanks for your quick response.
Michelle

[FDibbins]

in B2, copied down, try this....

=VLOOKUP(A2,INDIRECT("'"&$C$1&"'!A2:CN50"),MATCH(B1,'DR PT'!$A$1:$CN$1,0),FALSE)

[FDibbins]

i just noticed an error in that formula, it should have been....

=VLOOKUP(A2,INDIRECT("'"&$C$1&"'!A2:CN50"),MATCH(B1,INDIRECT("'"&$C$1&"'!$A$1:$CN$1"),0),FALSE)