How to eliminate #N/A or replace it?

[dennisli828]

So my boss ask me to find the data for S&P 500 in different time frame and put it on separate sheets, he also want me to make a main page so that he can choose different time frame with a button, and the corresponding sheet will pop out. I have been trying to figure this out with nested IF but excel said my function exceeded the limit. So I decide to just using the array formula and show "daily" if he chooses "daily". Of course, I highlight the entire column but say for annual data, the length is much shorter and therefore there will be alot of #N/A s. Nobody know how to deal with that?I wanna leave it blank or get rid of it. I have copied my formula, any suggestions? (daily, weekly etc are the names for the data table)

={IF($B$1="daily",daily,IF($B$1="weekly",weekly,IF($B$1="monthly",monthly,IF($B$1="quarterly",quarterly,IF($B$1="annually",annually," ")))))}

you can see the snapshot on:
https://picasaweb.google.com/dennisli828/Jul142011?authkey=Gv1sRgCKOy6r7Tz5qbVg#5629228931035234978

 

[Jonmo1]

That worked fine for me when I opened it with XL2007.

The picture you posted indicated you have XL2007.
But the file is in compatibility mode indicating you also use 2003..

So try

=IF(ISERROR(INDIRECT($B$1))," ",INDIRECT($B$1))

Also, make sure you are:
NOT typing in the { } by hand...
Instead, Highlight the ENTIRE range, and press CTRL + SHIFT + ENTER after entering the formula.

 

[Jonmo1]

Hang on, I see it now.

It's something to do with the way you have the entire range entered as a single array.

Try these instead, not requiring CTRL + SHIFT + ENTER

C2: =IF(ROWS(A$1:A1)>ROWS(INDIRECT($B$1)),"",INDEX(INDIRECT($B$1),ROWS(A$1:A1),1))
D2: =IF(ROWS(B$1:B1)>ROWS(INDIRECT($B$1)),"",INDEX(INDIRECT($B$1),ROWS(B$1:B1),2))

Both filled down as far as needed (you have till row 2900)

 

[dennisli828]

It works!! thank you so much!!

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[dennisli828]

yea it is hard to do a dynamic table sometime. and btw, do you know if there is any way to draw different graph base on the option (time frequency) I choose? I dont mind to use the original data to draw graph on different sheets but the problem is i have to figure out a certain way to move it to the first sheet when it is chosen.

 

[Jonmo1]

Sorry, charts aren't my thing..
You might make a new thread for that, I'm sure someone can help.