A different R1C1 vs a1 question

[Brad]

I would like to keep A1 (vs R1C1 format)

Cell H4 has the equation =column()

The next two cells in H have the equation
=IF(ISBLANK(INDIRECT(CHAR(64+$H$4-7)&5+INT((ROW()-5)/6))),"",INDIRECT(CHAR(64+$H$4-6)&5+INT((ROW()-5)/6)))

and

=IF(ISBLANK(INDIRECT(CHAR(64+$H$4-7)&5+INT((ROW()-5)/6))),"",INDIRECT(CHAR(64+$H$4-5)&5+INT((ROW()-5)/6)))

where the only difference is a -6 or a -5. In fact there are 6 different
equations (-6, -5, -4, -3, -2, -1)

This equation will work if the user doen't push the equations past column "Z"

What, if anything (except changing to R1C1 mode), can I do make this work if
they insert columns? Pushing the columns past column Z?

 

[Niek Otten]

Hi Brad,

You can use FALSE as 2nd argument in the INDIRECT function; then it will accept R1C1 addresses, while your workbook is still in A1
mode

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| I would like to keep A1 (vs R1C1 format)
|
| Cell H4 has the equation =column()
|
| The next two cells in H have the equation
| =IF(ISBLANK(INDIRECT(CHAR(64+$H$4-7)&5+INT((ROW()-5)/6))),"",INDIRECT(CHAR(64+$H$4-6)&5+INT((ROW()-5)/6)))
|
| and
|
| =IF(ISBLANK(INDIRECT(CHAR(64+$H$4-7)&5+INT((ROW()-5)/6))),"",INDIRECT(CHAR(64+$H$4-5)&5+INT((ROW()-5)/6)))
|
| where the only difference is a -6 or a -5. In fact there are 6 different
| equations (-6, -5, -4, -3, -2, -1)
|
| This equation will work if the user doen't push the equations past column "Z"
|
| What, if anything (except changing to R1C1 mode), can I do make this work if
| they insert columns? Pushing the columns past column Z?
| --
| Wag more, bark less

 

[Brad]

Thank you!