Is This Possible?

[Mike Langlois]

Hey everybody,

I have a question. I am aiming to build a spreadsheet where on the front sheet, someone can put a reference number in (lets say 001) and it will look through a series of other sheets and pull through any information that is in line with that reference number.

Normally I'd just use Vlookup, no problem, however there is a chance that there will be multiple lines for a reference number, and I'd need them to show each one somehow? Is something like this possible without using VBA?

Example sheet shows on sheet 1, you would put the ref number you want to pull out, and under the headers it would bring back any information that is under that ref on the other sheets?

 

[Hui]

It is a bit unclear exactly what you want

But you can use the Indirect function to build formula that lookup variable sheet names

To learn how to use Indirect have a read of Narrayank991's first post at: http://forum.chandoo.org/threads/what-is-indirect-function-solved.10688/

 

[prasaddn]

Hi Mike,

Check this file if it is what you were looking.

Regards,
Prasad DN

 

[Mike Langlois]

Hi Prasaddn,

Thanks for your help. That does appear to be along the lines of what I am looking for. One question however, is that if there is a reference column that goes 1 1 1 2 2 1 1 1 1 it works and brings back all the 1's, however it will leave blank the two cells that are 2, and leave the gap there?

Is there a way so that it wouldn't leave blank spaces?