MrExcel.com - Your One Stop for Excel Tips & Solutions

cell formatting, #value!

Posted by steven scaife on September 27, 2001 4:15 AM

i am using the formula below which someone has suggested to me and this removes the zero from the column if the value it is looking at is null.
=IF(Sheet1!B1,Sheet1!B1,"")

this is fine when it pulls a number through, but when its text it places #value! in the box instead of the text string.

to resolve this i am trying to use the following bit of vba code in a function on a button.

Dim formval As String
Dim celltxt As String

formval = Selection.Formula
MsgBox formval
celltxt = Selection.Value
MsgBox celltxt
'If Selection.Value = 0 Then
'Selection.Value = ""
'End If
If Selection.Value = 0 Then
Selection.Value = "=" & "if(selection.formula, selection.formula,"""")"
End If

i am trying to get it so that it places the following formula in the box

=IF(Sheet1!B1,Sheet1!B1,"")

where b1 is the cell to lookup the value that cell reference will change in each cell.

the formula in the cell is =sheet1!b1

sorry to go on for so long but any ideas
if so thanks in advance

steve

Posted by Robb on September 27, 2001 4:29 AM

Stephen

Try using this in place of the original:

=IF(ISNUMBER(Sheet1!F1),IF(Sheet1!F1,Sheet1!F1,""),Sheet1!F1)

Any help?

Regards

Posted by Robb on September 27, 2001 4:30 AM

Posted by steven scaife on September 27, 2001 4:40 AM

Thats fine with the text strings as it now pulls the text through, but if any cells are empty then a zero is placed in the box. The formula is dragged down the column as well.

any ideas again

cheers

steve

Posted by Robb on September 27, 2001 5:04 AM

Steve

This should fix the empty cell issue - I'm not sure whether
what you mean by the "formula is dragged down the column as well".
Is that also an issue to fix?

Try this though:

=IF(Sheet1!F1="","",IF(ISNUMBER(Sheet1!F1),IF(Sheet1!F1,Sheet1!F1,""),Sheet1!F1))

Any help?

Regards

Thats fine with the text strings as it now pulls the text through, but if any cells are empty then a zero is placed in the box. The formula is dragged down the column as well. any ideas again cheers

Posted by steven scaife on September 27, 2001 5:15 AM

cheers thats working now.

the "formula is dragged down the column as well".
would go like
cell b1 would have the formula =IF(Sheet1!F1="","",IF(ISNUMBER(Sheet1!F1),IF(Sheet1!F1,Sheet1!F1,""),Sheet1!F1))
cell b2 would have the formula =IF(Sheet1!F2="","",IF(ISNUMBER(Sheet1!F2),IF(Sheet1!F2,Sheet1!F2,""),Sheet1!F2))

etc etc. just in case you was interested. cheers anyways.

steve

Posted by Robb on September 27, 2001 5:21 AM

the "formula is dragged down the column as well".

Posted by Robb on September 27, 2001 5:22 AM

Posted by Aladin Akyurek on September 27, 2001 9:50 AM

: i am using the formula below which someone has suggested to me and this removes the zero from the column if the value it is looking at is null.


Steven & Robb,

I was that one. I didn't consider the possibilty of non-numeric values. A shorter version that would also cope with non-numeric values is:

=IF(LEN(Sheet1!B1),Sheet!B1,"")

Aladin