=VLOOKUP(A12,INDIRECT("'"&INDEX({"Shoes";"Girls Sandals";"Mens
Sandals";"Tops";"Bottoms";"Accessories"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Shoes";"Girls
Sandals";"Mens
Sandals";"Tops";"Bottoms";"Accessories"}&"'!A12:A5 00"),A12)0),0))&"'!A12:F500"),2,0
)

Im using the above formula to trace info from multiple sheets, which
works amazingly. Thanks guys for sharing your knowledge!!!

At the moment if i dont have any data entered into a cell in column A i
get N/A# in the cell with the formula... is there a way to have the
cell display nothing until the user enters data??

Thanks


--
George.
------------------------------------------------------------------------
George.'s Profile: http://www.excelforum.com/member.php...o&userid=35028
View this thread: http://www.excelforum.com/showthread...hreadid=549336

You may wrap all the formula in an IF statement, like:
=IF(A12="","",original formula)

Hope this helps,
Miguel.

"George." wrote:


=VLOOKUP(A12,INDIRECT("'"&INDEX({"Shoes";"Girls Sandals";"Mens
Sandals";"Tops";"Bottoms";"Accessories"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Shoes";"Girls
Sandals";"Mens
Sandals";"Tops";"Bottoms";"Accessories"}&"'!A12:A5 00"),A12)0),0))&"'!A12:F500"),2,0
)

Im using the above formula to trace info from multiple sheets, which
works amazingly. Thanks guys for sharing your knowledge!!!

At the moment if i dont have any data entered into a cell in column A i
get N/A# in the cell with the formula... is there a way to have the
cell display nothing until the user enters data??

Thanks


--
George.
------------------------------------------------------------------------
George.'s Profile: http://www.excelforum.com/member.php...o&userid=35028
View this thread: http://www.excelforum.com/showthread...hreadid=549336

you, my friend, are a lord!!
it worked a treat, better yet, i figured out why it worked,, wooohooo
finally learning a little bit on this program.

thanks for your help appreciate it.

George.


--
George.
------------------------------------------------------------------------
George.'s Profile: http://www.excelforum.com/member.php...o&userid=35028
View this thread: http://www.excelforum.com/showthread...hreadid=549336