Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A different R1C1 vs a1 question
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A different R1C1 vs a1 question
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 "Brad" wrote in message ... | 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
A different R1C1 vs a1 question
Thank you!
-- Wag more, bark less "Niek Otten" wrote: 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 "Brad" wrote in message ... | 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A different R1C1 vs A1 question | Excel Discussion (Misc queries) | |||
R1C1 formula question | Excel Worksheet Functions | |||
Question on this conversion code switching between r1c1 to A1 format tia sal2 | Excel Worksheet Functions | |||
Question on this conversion code switching between r1c1 to A1 format tia sal2 | Excel Worksheet Functions | |||
Question About Accessing Web Info Using R1C1 | Excel Worksheet Functions |