PDA

View Full Version : [SOLVED] Format Cell



pehl
12-22-2004, 11:02 AM
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
12-22-2004, 11:18 AM
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
12-22-2004, 12:04 PM
Gsouza, thank you!
pehl

Zack Barresse
12-22-2004, 12:06 PM
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

pehl
12-22-2004, 12:33 PM
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
12-22-2004, 12:37 PM
Could you give me more detail in what your formula is and where you want the value returned. Give more detail please

pehl
12-22-2004, 12:48 PM
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
12-22-2004, 01:03 PM
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
12-22-2004, 01:16 PM
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
12-22-2004, 01:41 PM
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
12-22-2004, 01:57 PM
Hi kpuls--the formula doesn't appear to have any effect.
Thanks,
pehl

Ken Puls
12-22-2004, 02:03 PM
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
12-22-2004, 02:50 PM
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
12-22-2004, 02:56 PM
Any way you can post an example workbook up?

pehl
12-22-2004, 03:10 PM
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
12-22-2004, 03:13 PM
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
12-22-2004, 03:14 PM
You're welcome! Glad it worked out!