How to delete data and avoid reference errors

[Brandon10]

Hi MrExcel and others,

I have some about 90 rows of data with 7 columns. The raw data is found in the first column and then the other columns contain formulas based on the first column of raw data. So column A contains a bunch of numbers and column b: B1=A1+1, B2=A2+1 etc...

I'm trying to be able to delete data from the first column and shift the cells up, but the columns next to it don't seem to adapt and give me a REF error.

So specifically, cell A1=5 and A2=6. Cell B1=A1+1, and when I delete cell A1 and shift the cells up, A1 becomes A2=6 and B1 should be equal to the new A1+1 so it should be 6+1=7, but I get B1=#REF!+1 instead.

Is there a way around this?...I feel like it is potentially obvious.

Thanks!
Brandon

 

[Jonmo1]

Welcome to the board...

Delete Column B along with Column A..

so if you're wanting to delete A5 and A6, Delete row 5 and 6 from BOTH A and B at the same time.

That should work..

 

[BAlGaInTl]

as jonmo1 says... you can delete them all together.

Another option may be to use offset instead of a direct reference.

I put the following in B1 and it seemed to work...

=OFFSET(B1,0,-1)+1

 

[Brandon10]

Thanks guys,

Deleting the entire row definitely works, and I think offset is the way to go when trying to delete a block.


Brandon