I am reading values from several worksheets that are compiling on a front
worksheet. However, when I try to sum those values my formula is not
recognizing the later cell values.
=IF(AB20,AB2,P2+S2+V2+Y2)
Anyone experience this and how did you resolve it?
By the way, I'm running Excel 07.
Thanks

Hi,

There's nothing wrong with the formula so if AB20 evaluates as false then
it will sum the 4 cells providing they are numbers but if any are text you
will get a #VALUE! error. What error are you getting?

Mike

"mickey" wrote:

I am reading values from several worksheets that are compiling on a front
worksheet. However, when I try to sum those values my formula is not
recognizing the later cell values.
=IF(AB20,AB2,P2+S2+V2+Y2)
Anyone experience this and how did you resolve it?
By the way, I'm running Excel 07.
Thanks

You say:

"I am reading values from several worksheets that are compiling on a front
worksheet..."

If this is the case then I'd expect to see the sheet name referenced in your
formula, such as;

=C2+Sheet2!C2+Sheet3!C2



"mickey" wrote in message
...
I am reading values from several worksheets that are compiling on a front
worksheet. However, when I try to sum those values my formula is not
recognizing the later cell values.
=IF(AB20,AB2,P2+S2+V2+Y2)
Anyone experience this and how did you resolve it?
By the way, I'm running Excel 07.
Thanks

I'm getting a blank cell. The cells are all formatted as accounting. If I
change the formula from a 0 to "" I do get the #VALUE! error.

"Mike H" wrote:

Hi,

There's nothing wrong with the formula so if AB20 evaluates as false then
it will sum the 4 cells providing they are numbers but if any are text you
will get a #VALUE! error. What error are you getting?

Mike

"mickey" wrote:

I am reading values from several worksheets that are compiling on a front
worksheet. However, when I try to sum those values my formula is not
recognizing the later cell values.
=IF(AB20,AB2,P2+S2+V2+Y2)
Anyone experience this and how did you resolve it?
By the way, I'm running Excel 07.
Thanks

Hi,

Then I think you have a couple of issues. Changing 0 to "" evaluates as
false and because you get a value error then some or all of your numbers in
P2 etc seem to be text. Try this for each of those cells

=isnumber(P2) and each should evaluate as true. If any evaluate as false
then they are text.

second consider AB2 is it really <0 or is there a small number that the
formatting of your formula cell or of AB2 stops you from seeing.

Mike

"mickey" wrote:

I'm getting a blank cell. The cells are all formatted as accounting. If I
change the formula from a 0 to "" I do get the #VALUE! error.

"Mike H" wrote:

Hi,

There's nothing wrong with the formula so if AB20 evaluates as false then
it will sum the 4 cells providing they are numbers but if any are text you
will get a #VALUE! error. What error are you getting?

Mike

"mickey" wrote:

I am reading values from several worksheets that are compiling on a front
worksheet. However, when I try to sum those values my formula is not
recognizing the later cell values.
=IF(AB20,AB2,P2+S2+V2+Y2)
Anyone experience this and how did you resolve it?
By the way, I'm running Excel 07.
Thanks

If I remove the statement and try to sum the 4 cells with a simple A+B+C+D
formula I get the #VALUE! error.
????

"mickey" wrote:

I'm getting a blank cell. The cells are all formatted as accounting. If I
change the formula from a 0 to "" I do get the #VALUE! error.

"Mike H" wrote:

Hi,

There's nothing wrong with the formula so if AB20 evaluates as false then
it will sum the 4 cells providing they are numbers but if any are text you
will get a #VALUE! error. What error are you getting?

Mike

"mickey" wrote:

I am reading values from several worksheets that are compiling on a front
worksheet. However, when I try to sum those values my formula is not
recognizing the later cell values.
=IF(AB20,AB2,P2+S2+V2+Y2)
Anyone experience this and how did you resolve it?
By the way, I'm running Excel 07.
Thanks

Mike:
I formatted each cell as a number and rewrote the formula to =P2+S2+V2+Y2
and I get a #VALUE! error.

"Mike H" wrote:

Hi,

Then I think you have a couple of issues. Changing 0 to "" evaluates as
false and because you get a value error then some or all of your numbers in
P2 etc seem to be text. Try this for each of those cells

=isnumber(P2) and each should evaluate as true. If any evaluate as false
then they are text.

second consider AB2 is it really <0 or is there a small number that the
formatting of your formula cell or of AB2 stops you from seeing.

Mike

"mickey" wrote:

I'm getting a blank cell. The cells are all formatted as accounting. If I
change the formula from a 0 to "" I do get the #VALUE! error.

"Mike H" wrote:

Hi,

There's nothing wrong with the formula so if AB20 evaluates as false then
it will sum the 4 cells providing they are numbers but if any are text you
will get a #VALUE! error. What error are you getting?

Mike

"mickey" wrote:

I am reading values from several worksheets that are compiling on a front
worksheet. However, when I try to sum those values my formula is not
recognizing the later cell values.
=IF(AB20,AB2,P2+S2+V2+Y2)
Anyone experience this and how did you resolve it?
By the way, I'm running Excel 07.
Thanks

Formatting as a number won't change anything, if the cell contains text then
text it remains. How are these 'numbers' derived, what's the formula?

Mike

"mickey" wrote:

Mike:
I formatted each cell as a number and rewrote the formula to =P2+S2+V2+Y2
and I get a #VALUE! error.

"Mike H" wrote:

Hi,

Then I think you have a couple of issues. Changing 0 to "" evaluates as
false and because you get a value error then some or all of your numbers in
P2 etc seem to be text. Try this for each of those cells

=isnumber(P2) and each should evaluate as true. If any evaluate as false
then they are text.

second consider AB2 is it really <0 or is there a small number that the
formatting of your formula cell or of AB2 stops you from seeing.

Mike

"mickey" wrote:

I'm getting a blank cell. The cells are all formatted as accounting. If I
change the formula from a 0 to "" I do get the #VALUE! error.

"Mike H" wrote:

Hi,

There's nothing wrong with the formula so if AB20 evaluates as false then
it will sum the 4 cells providing they are numbers but if any are text you
will get a #VALUE! error. What error are you getting?

Mike

"mickey" wrote:

I am reading values from several worksheets that are compiling on a front
worksheet. However, when I try to sum those values my formula is not
recognizing the later cell values.
=IF(AB20,AB2,P2+S2+V2+Y2)
Anyone experience this and how did you resolve it?
By the way, I'm running Excel 07.
Thanks

They are copied and pasted from another workbook.

"Mike H" wrote:

Formatting as a number won't change anything, if the cell contains text then
text it remains. How are these 'numbers' derived, what's the formula?

Mike

"mickey" wrote:

Mike:
I formatted each cell as a number and rewrote the formula to =P2+S2+V2+Y2
and I get a #VALUE! error.

"Mike H" wrote:

Hi,

Then I think you have a couple of issues. Changing 0 to "" evaluates as
false and because you get a value error then some or all of your numbers in
P2 etc seem to be text. Try this for each of those cells

=isnumber(P2) and each should evaluate as true. If any evaluate as false
then they are text.

second consider AB2 is it really <0 or is there a small number that the
formatting of your formula cell or of AB2 stops you from seeing.

Mike

"mickey" wrote:

I'm getting a blank cell. The cells are all formatted as accounting. If I
change the formula from a 0 to "" I do get the #VALUE! error.

"Mike H" wrote:

Hi,

There's nothing wrong with the formula so if AB20 evaluates as false then
it will sum the 4 cells providing they are numbers but if any are text you
will get a #VALUE! error. What error are you getting?

Mike

"mickey" wrote:

I am reading values from several worksheets that are compiling on a front
worksheet. However, when I try to sum those values my formula is not
recognizing the later cell values.
=IF(AB20,AB2,P2+S2+V2+Y2)
Anyone experience this and how did you resolve it?
By the way, I'm running Excel 07.
Thanks

You could upload a copy of your workbook to the link below and post the link

http://www.savefile.com/

Mike

"mickey" wrote:

They are copied and pasted from another workbook.

"Mike H" wrote:

Formatting as a number won't change anything, if the cell contains text then
text it remains. How are these 'numbers' derived, what's the formula?

Mike

"mickey" wrote:

Mike:
I formatted each cell as a number and rewrote the formula to =P2+S2+V2+Y2
and I get a #VALUE! error.

"Mike H" wrote:

Hi,

Then I think you have a couple of issues. Changing 0 to "" evaluates as
false and because you get a value error then some or all of your numbers in
P2 etc seem to be text. Try this for each of those cells

=isnumber(P2) and each should evaluate as true. If any evaluate as false
then they are text.

second consider AB2 is it really <0 or is there a small number that the
formatting of your formula cell or of AB2 stops you from seeing.

Mike

"mickey" wrote:

I'm getting a blank cell. The cells are all formatted as accounting. If I
change the formula from a 0 to "" I do get the #VALUE! error.

"Mike H" wrote:

Hi,

There's nothing wrong with the formula so if AB20 evaluates as false then
it will sum the 4 cells providing they are numbers but if any are text you
will get a #VALUE! error. What error are you getting?

Mike

"mickey" wrote:

I am reading values from several worksheets that are compiling on a front
worksheet. However, when I try to sum those values my formula is not
recognizing the later cell values.
=IF(AB20,AB2,P2+S2+V2+Y2)
Anyone experience this and how did you resolve it?
By the way, I'm running Excel 07.
Thanks

Here is the formula that reads the other worksheets:
='47'!$C$40

"Mike H" wrote:

Formatting as a number won't change anything, if the cell contains text then
text it remains. How are these 'numbers' derived, what's the formula?

Mike

"mickey" wrote:

Mike:
I formatted each cell as a number and rewrote the formula to =P2+S2+V2+Y2
and I get a #VALUE! error.

"Mike H" wrote:

Hi,

Then I think you have a couple of issues. Changing 0 to "" evaluates as
false and because you get a value error then some or all of your numbers in
P2 etc seem to be text. Try this for each of those cells

=isnumber(P2) and each should evaluate as true. If any evaluate as false
then they are text.

second consider AB2 is it really <0 or is there a small number that the
formatting of your formula cell or of AB2 stops you from seeing.

Mike

"mickey" wrote:

I'm getting a blank cell. The cells are all formatted as accounting. If I
change the formula from a 0 to "" I do get the #VALUE! error.

"Mike H" wrote:

Hi,

There's nothing wrong with the formula so if AB20 evaluates as false then
it will sum the 4 cells providing they are numbers but if any are text you
will get a #VALUE! error. What error are you getting?

Mike

"mickey" wrote:

I am reading values from several worksheets that are compiling on a front
worksheet. However, when I try to sum those values my formula is not
recognizing the later cell values.
=IF(AB20,AB2,P2+S2+V2+Y2)
Anyone experience this and how did you resolve it?
By the way, I'm running Excel 07.
Thanks

Here what I do to simple convert text with numeric value into a number
(on error set to skip casual text):
For Each c In ActiveSheet.UsedRange.Cells
On Error Resume Next
If c < "" Then
c = CDbl(c)
End If
Next c
On Error GoTo 0

Another method... Select all the cells containing "text numbers", Cell
Format them to General and then execute this command in VB's Immediate
Window...

Selection.Formula = Selection.Formula

If you want that as a macro...

Sub MakeTextNumbersToRealNumbers()
With Selection
.NumberFormat = "General"
.Formula = .Formula
End With
End Sub

Just select the cells to be converted and run the macro.

--
Rick (MVP - Excel)


"NOPIK" wrote in message
...
Here what I do to simple convert text with numeric value into a number
(on error set to skip casual text):
For Each c In ActiveSheet.UsedRange.Cells
On Error Resume Next
If c < "" Then
c = CDbl(c)
End If
Next c
On Error GoTo 0