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

 

[Dannyh1]

Hi,

Try using INDIRECT instead of multiple IF's

=INDIRECT("'"&$B$1&"'!A1)

If B1 was daily, it would look at daily sheet cell A1

 

[yogeshmaney]

use

=if(iserror(your formula),"",your formula)

 

[Jonmo1]

Try

=IF(ISERROR(INDIRECT(B1)),"",INDIRECT(B1))

Or in XL2007

=IFERROR(INDIRECT(B1),"")

 

[dennisli828]

So where do i plug in my formula?

 

[Jonmo1]

So where do i plug in my formula?

The same place you put the original formula you posted...

The formula I posted is intended to replace the one you posted...

In other words...
This
={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," ")))))}

Can be replaced with
=INDIRECT(B1)

Then to test it for error,
=IFERROR(INDIRECT(B1),"")


Hope that helps.

 

[dennisli828]

the problem persists for some reason, you mind I send you the file so you can have a look?

 

[yogeshmaney]

please put it on any online storage

 

[Jonmo1]

the problem persists for some reason, you mind I send you the file so you can have a look?

Sent you a P.M.

 

[sheetspread]

If there's no other error type you can use goto (F5-special) to select and delete just the cells with formula errors