Another Nesting IF Statement Problem

[bigwilly11189]

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'!A1>0,'applesub'A1,if($b$1="Banana", if('bananasub'!A1>0,'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.

[Biff]

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" <bigwilly11189.1v46if_1126307129.4463@excelforum-nospam.com>
wrote in message
news:bigwilly11189.1v46if_1126307129.4463@excelforum-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'!A1>0,'applesub'A1,if($b$1="Banana",
> if('bananasub'!A1>0,'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
>

[bigwilly11189]

Alright I'll try to explain a little more. When you enter Say "Charter" into Cell B2 on the results worksheet, all the accounts that are in "Charter" show up in a vertical list, where each account is in its own cell, these accounts are all listed on another worksheet. At the same time all the cashflows with dates appear on the Cashflow worksheet where on the "Cashflow" worksheet account number one will be in A3, then Cashflow 1 amount will be in B3, the date of Cashflow 1 will be in cell C3, then Cashflow 2 will be in D3 and the Date in E3 and so on. If you can tell me how to tell excel that when say "Charter" is entered into Cell B2 on the "Results" worksheet, it automatically looks for a certain worksheet to pull the data from for 3 other worksheets. But if I entere say Comcast in cell B2 it will pull the data from a different 3 worksheets. This is why the IF statements work so well, b/c I can say If B2 equals this, then pull the data from this worksheet, if not then if it equals anotehr name then pull the data from another worksheet and so on. I tried the consatenate (sp??) function which would work if I could somehow get it not to be as a text. I need the Cashflows as $'s and the Dates as Dates and not text which they come out as when I use that function. I hope this clarified things a little.

[bigwilly11189]

anyone??? What I want to be able to do is Cell B1 on the main worksheet has a drop down list, when you pick something in that list, it pulls information from the worksheets that correspond with that word or phrase and puts the information on a different worksheet. Can this be done? The information is Data and not text. Can I do a VBA function?