Setting up a dynamically generated range reference

Originally Posted by cveetan

...
So imagine it like this

Row6: SUMIF($AG$5:$AM$5,"ITF",INDIRECT(CONCATENATE("AG","6:","AM","6")))

"AG" replaced by IF(33,CHAR(ROUNDDOWN((33-1)/26,0)+64)&CHAR(MOD((33-1),26)+65),CHAR(33+64))
"AM" replaced by IF(COLUMN(),CHAR(ROUNDDOWN((COLUMN()-1)/26,0)+64)&CHAR(MOD((COLUMN()-1),26)+65),CHAR(COLUMN()+64))
...

I've tried these as they are in various cells in a blank sheet. The first returns AG wherever it is placed, as "IF(33" is the same as saying "IF(TRUE" so it will always return AG - in that case why not replace it with "AG" ?

The second expression is incomplete - I think it should be:

=IF(COLUMN()>26,CHAR(ROUNDDOWN((COLUMN()-1)/26,0)+64)&CHAR(MOD((COLUMN()-1),26)+65),CHAR(COLUMN()+64))

and this will return the column identifier for whichever column it is in (you might have considered the ADDRESS function).

@Pete, I've tried your way also but couldn't make it work it gives #REF! error when calculating though CTRL + SHIFT + ENTER

I'm not really sure why you would need CSE for this, but I think your amended formula should be (taking into account what I've said above):

=SUMIF($AG$5:$AM$5,"ITF",INDIRECT("AG"&ROW(A6)&":"&IF(COLUMN()>26,CHAR(ROUNDDOWN((COLUMN()-1)/26,0)+64)&CHAR(MOD((COLUMN()-1),26)+65),CHAR(COLUMN()+64))&ROW(A6)))

You had your quotes in the wrong place.

Hope this helps.

Pete

Thanks Pete!


All fixed now! I moved the quotes as you suggested and it worked like a charm.

You are totally right about the static 33 formula, I've used it just as an example, cause I plan to place a more complex one in its place which shall not be a problem now that I have the placement of the second one resolved. ; )

Thanks again for your assistance! Cheers pal!

Cvetan

Glad to be of help. Perhaps you can mark the thread as Solved, and if there is any post that has particularly helped you then you can pass on thanks directly by clicking the "star" icon in the bottom left corner.

Pete