Variable cell reference in formula

[BiggusDoggus]

Hi all

I have this formula:

{=IF(AQ11="","",IFERROR(SUM(INDEX($B11:$T11,MATCH(FALSE,ISBLANK($B11:$T11),0)+2):T11),""))}

What I'm wanting is the "T" in the ranges be a cell value. Specifically, AV11.

To clarify - cell AV11 will have a letter (based on another formula, not relevant to this question). In essence, when the new month starts AV11 will display "U". So I want the T's be replaced by U's automatically.

Can this be done please?

 

[fhqwgads]

something with INDIRECT maybe?

replace $T11 with INDIRECT(AV11&11)

 

[BiggusDoggus]

something with INDIRECT maybe?

replace $T11 with INDIRECT(AV11&11)

Excellent - thank you!

Just a minor change - I couldn't have it as "&11" as when the cell formula is copied down it stays @ 11, instead of 12,13,14, etc etc. So I just added another column, AW, with 11,12,13,14 etc.

So:

{=IF(AQ11="","",IFERROR(SUM(INDEX($B11INDIRECT(AV11&AW11)),MATCH(FALSE,ISBLANK($B11INDIRECT(AV11&AW11))),0)+2)INDIRECT(AV11&AW11))),""))}

Copied down ad nauseam..


Thanks for that!

 

[fhqwgads]

what row is your formula going into?

you can use ROW() to give you the the row number that the formula is currently in, so for example if you put =ROW() in any cell in row 11 the formula will return 11

in your formula it would be something like INDIRECT(AV11&ROW()) and then you wouldnt need the reference column AW

 

[BiggusDoggus]

what row is your formula going into?

you can use ROW() to give you the the row number that the formula is currently in, so for example if you put =ROW() in any cell in row 11 the formula will return 11

in your formula it would be something like INDIRECT(AV11&ROW()) and then you wouldnt need the reference column AW

You learn something new every day - I will do that. Thanks!