#ref error 1

[SharonMee]

Hello all:

I hope you can help me with the following:

In cell C4, I have the following formula: $G$5.

Sometimes I need to delete columns G to K, such that columns L to P shifts into place i.e. now becomes column G to K.

Can you please tell me why I am getting this error since column G still exists. How can I prevent this error, i.e. what other formula apart from $G$5 to prevent the #Ref error?

Thanks so much for helping.


Sharon

Thanks so much

 

[xlbo]

There is no way to prevent this error without using another formula - it is a REFERENCE error and it means that you are trying to reference something that isn't there. Column G may still be there in that excel will move columns back to compensate but the column you WERE referencing is gone.

The only way around this is to hold the range reference as a STRING and use the INDIRECT function eg

if you had =$G$5
the new formula would be =INDIRECT("$G$5")

Because the range ref is held as a string, it does not change or move about when columns / rows are deleted inserted etc and will therefore work

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SharonMee]

Thanks so much, that worked perfectly.


Sharon

 

[macropod]

You can achieve much the same result with an OFFSET formula. For example:
=OFFSET($A$1,4,6)
references G5 also. The OFFSET function also takes two more parameters for #rows and # columns, for defining a range, for example:
=SUM(OFFSET($A$1,4,6,3,1))
could be used to sum G5:H7. This works fine - unless you delete Column A and/or Row 1.

Cheers