Format Cell

[pehl]

Hi,
I have cells referencing a calculation from a different worksheet, however, it returns #N/A if there is nothing to reference. Can someone please tell me how to change this to a blank or a dash?
Thank you,
pehl

[gsouza]

In another cell put a formula

=ISNA(F5)

or whateveer cell your calculation is in. In that cell it will be either true or false. You can use conditional formatting to make the font on your calculated cell WHITE depending on weather the cell with =ISNA(F5) is true or false. This is simple but works.

[pehl]

Gsouza, thank you!
pehl

[Zack Barresse]

Glad you got it sorted pehl!! Did you know you can mark your own thread(s) solved? Just go to Thread Tools (top of the thread) --> Mark Solved --> Perform Action. This is courtesy of our Board Coder Mark007!

[pehl]

Sorry, I spoke too soon--if the result is false, how do I have it return the value rather than "false"?
Thanks again,
pehl

[gsouza]

Could you give me more detail in what your formula is and where you want the value returned. Give more detail please

[pehl]

The cell formula is ='SHEET 1'!F20. It refers to a cell from a different worksheet with the following formula:

=INDEX(E41:E51,MATCH(9.999999999E+307,E41:E51))

Thank you,
pehl

[gsouza]

Okay I understand your formula, it places whatever ever number is placed in the last cell in range E41:E51 and it has to be a number of course. On SHEET 1'!F20 it would only be #N/A if there is nothing in E41:E51 or just text. So how can you return the value other then "false" if there is no value in E41:E51? Maybe I am missing somthing.

[Ken Puls]

Hi there,

Untested, but try this:

=if(isna(INDEX(E41:E51,MATCH(9.999999999E+307,E41:E51))),"", INDEX(E41:E51,MATCH(9.999999999E+307,E41:E51)))

HTH,

[pehl]

I want to use the same conditional formatting on all cells as they may or may not have a value to reference, so I guess I can't use the ISNA function? (I want it to reference whatever is in the cell or I want it to be blank [or a dash]).

Thank you,
pehl

[pehl]

Hi kpuls--the formula doesn't appear to have any effect.
Thanks,
pehl

[Ken Puls]

Sorry, Pehl,

I just reread the post, and think I picked on the wrong one. To confirm, it's the formula ='SHEET 1'!F20 that may return the N/A value?

If so, try

=if(isna('SHEET 1'!F20,"", 'SHEET 1'!F20))

Let us know,

[pehl]

Actually, it returns the N/A on Sheet 1 where the original formula is. (The last formula returns an error re: the two quotation marks.)
Thank you,
pehl

[Ken Puls]

Any way you can post an example workbook up?

[pehl]

I retested the formula

=if(isna(INDEX(E41:E51,MATCH(9.999999999E+307,E41:E51))),"", INDEX(E41:E51,MATCH(9.999999999E+307,E41:E51)))

on the original sheet and reentered the reference to it and it reports a value or a blank, so it's all good. Thanks so much kpuls and gsouza!
pehl

[Zack Barresse]

Hello pehl,


I see Ken has you a good solution. Do you have the morefunc add-in installed? If so, you can make use of some other functions and truncate to ...

=IF(ISNA(SETV(INDEX(E41:E51,MATCH(9.999999999E+307,E41:E51)))),"",GETV())

Which is far less expensive than what you have currently.

Or in another cell (we'll say B1) put ..

=MATCH(9.999999999E+307,E41:E51)

Then use (we'll say in B2) ...

=IF(ISNA(B1),"",INDEX(E41:E51,B1))

Or better yet, define the name BigNum (a standardized name) which Refers To:

=MATCH(9.999999999E+307,E41:E51)

Then your function can be (which would be a better "one cell" approach) ..

=IF(ISNA(BigNum),"",INDEX(E41:E51,BigNum))

HTH

[Ken Puls]

You're welcome! Glad it worked out!