I have an excel spreadsheet linked to a network printer, it contains a
list of what each user printed, how many pages, and the total cost. It
is constantly updated. The total cost column contains the wrong price,
so Im using this formula: =SUM(D2/C2)*(H2) in EACH field on the total
user cost column to extract the CORRECT price. So, I started my macro,
highlighted the entire TOTAL COST column, then inserted my formula into
each field in that column. The correct price displays for rows that
contain data, but I am getting "#DIV/0!" on the rows that do not
contain data. This is fine, but when I try to create a SUBTOTAL (Data
-- Subtotals) for each user, I get the following error:

"To prevent possible loss of data, Microsoft Excel cannot shift
nonblank cells off the worksheet."

Can you help me write an IF statement that basically says: IF there is
data in this field than use the formula =SUM(D2/C2)*(H2), and if there
is NOT data in this field then leave the cell blank? Thank you...


--
Abe85
------------------------------------------------------------------------
Abe85's Profile: http://www.excelforum.com/member.php...o&userid=35270
View this thread: http://www.excelforum.com/showthread...hreadid=551059

=If(C2=0,0,SUM(D2/C2)*(H2))

or

=If(C2=0,"",SUM(D2/C2)*(H2))


depending on what you want to display. If you put the formula in with a
macro, then second would be
..Formula = "=If(C2=0,"""",SUM(D2/C2)*(H2))"

--
Regards,
Tom Ogilvy


"Abe85" wrote:


I have an excel spreadsheet linked to a network printer, it contains a
list of what each user printed, how many pages, and the total cost. It
is constantly updated. The total cost column contains the wrong price,
so Im using this formula: =SUM(D2/C2)*(H2) in EACH field on the total
user cost column to extract the CORRECT price. So, I started my macro,
highlighted the entire TOTAL COST column, then inserted my formula into
each field in that column. The correct price displays for rows that
contain data, but I am getting "#DIV/0!" on the rows that do not
contain data. This is fine, but when I try to create a SUBTOTAL (Data
-- Subtotals) for each user, I get the following error:

"To prevent possible loss of data, Microsoft Excel cannot shift
nonblank cells off the worksheet."

Can you help me write an IF statement that basically says: IF there is
data in this field than use the formula =SUM(D2/C2)*(H2), and if there
is NOT data in this field then leave the cell blank? Thank you...


--
Abe85
------------------------------------------------------------------------
Abe85's Profile: http://www.excelforum.com/member.php...o&userid=35270
View this thread: http://www.excelforum.com/showthread...hreadid=551059

THAT WORKED!! Thank yo

--
Abe8
-----------------------------------------------------------------------
Abe85's Profile: http://www.excelforum.com/member.php...fo&userid=3527
View this thread: http://www.excelforum.com/showthread.php?threadid=55105

Whoops, I spoke too soon. It does not show data in fields which do no
contain data, which I want... but when I try to apply Subtotals, i
gives me the same error message explaining:

"To prevent possible loss of data, Microsoft Excel cannot shif
nonblank cells off the worksheet."

I guess this is because even though the cells are blank, they stil
contain a formula.

Any suggestions?

Thanks again

--
Abe8
-----------------------------------------------------------------------
Abe85's Profile: http://www.excelforum.com/member.php...fo&userid=3527
View this thread: http://www.excelforum.com/showthread.php?threadid=55105

That error doesn't have anthing to do with the formula. My guess is it is
caused by subtotal trying to hide rows.


This is the article that came to my mind when you cited the error:

http://support.microsoft.com/kb/211769/en-us
"Cannot shift objects off sheet" error message when you hide columns in Excel

Perhaps it will give you some insights into the source of your problem.

--
Regards,
Tom Ogilvy


"Abe85" wrote:


Whoops, I spoke too soon. It does not show data in fields which do not
contain data, which I want... but when I try to apply Subtotals, it
gives me the same error message explaining:

"To prevent possible loss of data, Microsoft Excel cannot shift
nonblank cells off the worksheet."

I guess this is because even though the cells are blank, they still
contain a formula.

Any suggestions?

Thanks again.


--
Abe85
------------------------------------------------------------------------
Abe85's Profile: http://www.excelforum.com/member.php...o&userid=35270
View this thread: http://www.excelforum.com/showthread...hreadid=551059