Deleting Column Giving Me a #REF Error

[willi]

Hello everyone,

I have one worksheet with four quarters worth of income statements and a second worksheet with some forumlas based on the first. My problem comes in when I try to delete the oldest quarter of income statements. Everything shifts nicely over the the left but the formulas in the other worksheet are ruined with an #ref error.

If I am referencing cell "A3," before I delete that column, why can't it continue to reference "A3" with the new data?

Any help is desperately appreciated.

Thanks,
William

[Jack in the UK]

Sounds like you might have formula active referring to the soon to be deleted data column, so error !

Fix copy and paste special as values the far left (one before you deleted column) then delete.

This kills the formulas and should be OK

I suggest trying this on back up copy and play just in case so you still have original as it was.

HTH

[Mark W.]

I suspect that your statement, "I am referencing cell A3", means that you're using a formula such as...

=A3+B3+C3+D3

or...

=SUM(A3,B3,C3,D3)

Formulas with single cell references are quite intolerant of columns deletions and result in #REF! errors.

Formulas with cell ranges [e.g., =SUM(A3:D3)] don't produce #REF! errors when columns within their range are deleted, but the size of the range decreases [e.g., becomes =SUM(A3:C3) when column A:A is deleted] which probably won't suit your needs.

I'd recommend that you use the following...

=SUM(OFFSET(3:3,,,,4))

[ This Message was edited by: Mark W. on 2002-05-24 15:57 ]

[Yogi Anand]

Hi Mark:
Your suggested solution with the use of the SUM and OFFSET method works beautifully!
What are your thoughts on the use of the SUM and INDEX Function with the scenario you setup:

=SUM(INDEX(A3:D3,0,0,1))

Regards!

[Mark W.]

On 2002-05-24 16:35, Yogi Anand wrote:
Hi Mark:
Your suggested solution with the use of the SUM and OFFSET method works beautifully!
What are your thoughts on the use of the SUM and INDEX Function with the scenario you setup:

=SUM(INDEX(A3:D3,0,0,1))

Regards!

"Formulas with cell ranges [e.g., =SUM(A3:D3)] don't produce #REF! errors when columns within their range are deleted, but the size of the range decreases [e.g., becomes =SUM(A3:C3) when column A:A is deleted]..."

[ This Message was edited by: Mark W. on 2002-05-27 13:59 ]