Format Cell

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

In another cell put a formula

Code:

---

=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.

Gsouza, thank you!
pehl

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! :yes

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

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

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

Code:

---

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

---

Thank you,
pehl

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.

Hi there,

Untested, but try this:

Code:

---

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

---

HTH,

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

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

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

Code:

---

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

---

Let us know,

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

Any way you can post an example workbook up?

I retested the formula

Code:

---

=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

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 ...

Code:

---

=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 ..

Code:

---

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

---

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

Code:

---

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

---

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

Code:

---

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

---

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

Code:

---

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

---

HTH

You're welcome! Glad it worked out!