HELP! Conditional Formatting

[Abe85]

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

[Tom Ogilvy]

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

[Abe85]

THAT WORKED!! Thank you

[Abe85]

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.

[Tom Ogilvy]

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