Hi!

If everyone is now even more confused then when they started
reading then join the crowd and i'll try to explain more.

Hmmm.....

It's not real clear what you're trying to do but I did read something about
you trying a lookup.....

Maybe something along these lines:

=VLOOKUP(C1,INDIRECT(B1&"sub!A1:B5"),2,0)

???

Biff

"bigwilly11189"
wrote in message
news:bigwilly11189.1v46if_1126307129.4463@excelfor um-nospam.com...

Ok, I have read others problems but they haven't helped, so here is my
problem. I have a 4 main worksheets that pull data from the other 20+
worksheets in the Excel Book. I tried to use the Consatenate function
but that doesn't work b/c it returns it as text and that doesn't work,
i need it has data. What I'm trying to do is if cell B1 is equal to
either (names are irrelavent) Apple, Banana, Cherry, Date, Eggplant and
so on for up to 20+ names then it will pull data from a certain
column(s) in a certain worksheet. Each one has 3 worksheets
corresponding to it, so for Apple, it has 3 different worksheets that
correspond with it, each has a different function. So what I want to
do is something like this (without the limitation):

=if($b$1="Apple",if('applesub'!A10,'applesub'A1,i f($b$1="Banana",
if('bananasub'!A10,'bananasub'A1,.... and so on.

Now to make things easy I have 4 main worksheets that pull the data
from the other worksheets to make it easier for formulas. The data in
these 4 main worksheets will change when I enter Apple vs Banana in
cell B1. I have tried a lookup function but i don't know how to make
the vlookup function look at more than just one worksheet. Like if it
was Apple then lookup values on AppleSub worksheet, if it banana then
lookup values on BananaSub worksheet. The main page is my "results"
page. If everyone is now even more confused then when they started
reading then join the crowd and i'll try to explain more.

Thanks.


--
bigwilly11189
------------------------------------------------------------------------
bigwilly11189's Profile:
http://www.excelforum.com/member.php...o&userid=21527
View this thread: http://www.excelforum.com/showthread...hreadid=466426