Vlookup

=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:A500"),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

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:A500"),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.