(more) Excel Formula Help

[Homerboy]

I have multiple sheet in my excel document:

PURCHASED_DATA
MEDICAL_DATA
SPECIAL_DATA
etc..

I have a formula like:

=IF(ISERROR(INDEX(DATA!C:C,MATCH(DATA!$AI2,DATA!$AA:$AA,0))),"0",INDEX(DATA!C:C,MATCH(DATA!$AI2,DATA!$AA:$AA,0)))

I have a drop down menu in a cell that contains:
"Purchased"
"Medical"
"Special"
etc..

How can I construct the above formula to pull the data from the right sheet to match what is chose in the drop down?

Holy hell I realize that probably makes no sense... this is hard to explain.

 

[Homerboy]

got it:

=IF(ISERROR(INDEX(INDIRECT("'" &$Z$3&"_DATA"&"'!C:C"),MATCH($C7,INDIRECT("'" &$Z$3&"_DATA"&"'!$A:$A"),0))),"",INDEX(INDIRECT("'" &$Z$3&"_DATA"&"'!C:C"),MATCH($C7,INDIRECT("'" &$Z$3&"_DATA"&"'!$A:$A"),0)))

 

[KLin]

For the love of all that is holy, please stop using excel as a database program.

 

[Evadman]

Get rid of excel and use a database program.

 

[Markbnj]

Upgrade to Access.

[runs]