Help - Column delete, #ref! error

[mypantera]

Hi everyone,

I have a spreadsheet where the columns are numbered 1 through 14 (cells B4 through O4) then I drop down to cell O6 and that is numbered 15. From there I want to continue counting to B6 which would be 28 and this continues in the back and forth fashion. What I am trying to do is have the ability to delete a column and have the numbering recalculate and keep the numbers in order. I keep getting a #ref! error no matter how I try to do it.

here is what I would like it to look like on my spreadsheet:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
28 27 26 25 24 23 22 21 20 19 18 17 16 15
29 30....

then if I delete a column:
1 2 3 4 5 6 7 8 9 10 11 12 13
26 25 24 23 22 21 20 19 18 17 16 15 14
27 28...

Any help is appreciated

 

[CWatts]

Use the =COLUMN() function.

It's relative to its position in the worksheet which causes problems with some applications, but for what you're doing, it would be great

So Cell B4 would be =COLUMN()+1

 

[mypantera]

I did try the column() function and it worked perfectly for the first row only. Everything after that wouldn't work correctly. Thank you for he suggestion.

 

[vaniificat]

Put this in B4 and drag.

=IF(ROW()=4,COLUMN()-1,IF(MOD(ROW(),2)=1,B3-2*(COLUMN()-(COUNT($B$4:$O$4)+1))+1,B3+2*(COLUMN()-2)+1))

It works! No ref errors :D

You may have to expand the range given in the COUNT() function. Contraction (i.e. deletion of columns) is possible without any changes, though.

 

[mypantera]

Vaniificat,
I got it to work using a modified version of your code. THANK YOU! I forgot to mention that there is a space between each row... Here is how I modified it:

Cell(B4) =IF(ROW()=4,COLUMN()-1,IF(MOD(ROW(),2)=1,B3-2*(COLUMN()-(COUNT($B$4:$O$4)+2))+1,B3+2*(COLUMN()-2)+1))

Cell(b6) =IF(ROW()=4,COLUMN()-1,IF(MOD(ROW(),2)=0,B4-2*(COLUMN()-(COUNT($B$4:$N$4)+2))+1,B4+2*(COLUMN()-2)+1))

It repeats the =1 then =0 for every other row. I am not sure why exactly but for the first count() I had to use ($B$4:$O$4) but every other row after that I had to use count($B$4:$N$4)......

Again thank you very much