It’s just an INDIRECT and then a bunch of INDIRECTS within consecutive OFFSETS:
=INDIRECT("Main."&CELL("address"))&OFFSET(INDIRECT("Main."&CELL("address")),0,1)&OFFSET(INDIRECT("Main."&CELL("address")),0,2)&OFFSET(INDIRECT("Main."&CELL("address")),0,3)&OFFSET(INDIRECT("Main."&CELL("address")),0,4)
Since a lot of the formula is repeating, is there any way to shorten this? Even with a cheat or shortcut?
Here it is broken up into lines so you can see how repetitive it is:
=INDIRECT("Main."&CELL("address"))
&OFFSET(INDIRECT("Main."&CELL("address")),0,1)
&OFFSET(INDIRECT("Main."&CELL("address")),0,2)
&OFFSET(INDIRECT("Main."&CELL("address")),0,3)
&OFFSET(INDIRECT("Main."&CELL("address")),0,4)
For example, is there a way to put INDIRECT(“Main.”&CELL(“address”)) in cell A1 and then the formula would look much more condensed like this?:
=A1&OFFSET(A1,0,1)&OFFSET(A1,0,2)&OFFSET(A1,0,3)&OFFSET(A1,0,4)
Or even better something like this?:
=OFFSET(A1,0,0:4)
I know the last two examples don’t work but maybe you’ll see what I’m trying to accomplish.