Setting up a dynamically generated range reference

First of all, there is no need to use the CONCATENATE function - use the concatenation operator & instead.

Secondly, try this in row 6:

SUMIF($AG$5:$AM$5,"ITF",INDIRECT("AG"&ROW(A6)&":AM"&ROW(A6)))

The A6 will change as you copy it down and thus will return the range that you want.

Hope this helps.

Pete

Hey Pete, J, Martin,


Thank you for your quick replies and suggestions.

Surely I can enter the AG and AM manually

=SUMIF($AG$5:$AM$5,"ITF",AG6:AM6)

and then copy the formula down, however please remember that the "AG" and "AM" references are dynamically generated by formulas that need to be input to their places in this very SUMIF formula.

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))

and it looks like this

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

Sorry, I don't know any other way to insert dynamically changeable range of cell references, except for the CONCATENATE formula.
which works fine except for the static row number.

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

It looks like this:

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

Am i doing anything wrong? I believe this is the problematic area (in red)

SUMIF($AG$5:$AM$5,"ITF",INDIRECT("AG"&ROW(A6)&":AM"&ROW(A6)))

respectively in the originally suggested by me formula

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

So still wondering. If I manage to work it around I will let you know.


Cheers,

Cvetan

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