Is there any way to shorten this formula?

asked 2015-10-30 05:18:40 +0200

argh.pirate gravatar image

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.

edit retag flag offensive close merge delete