Dynamic Column Reference

TWHT

Hi!

Lets say I have a basic formula like this in a "budget" column:

=SUM([Week 1]@row:[Week 10]@row)

And I also have a cell in a helper column that includes this formula:

=WEEKNUMBER(TODAY())

Is there a way of [Week 10] changing to [Week X] based on the number in the week number cell? I have a column for every week of the year already in place. So if the weeknumber cell reads 28 for example, I'd want the formula to give the result of this, effective, equation:

=SUM([Week 1]@row:[Week 28]@row)

The way I tried to do it was like this, but I could be going in the complete wrong direction.

=SUM(INDEX([Week 1]@row:[Week 52]@row, 1, 1):INDEX([Week 1]@row:[Week 52]@row, 1, [Current Week]2))

But it just gives an unparseable error.

Thanks in advance!

Paul Newcome

You can do this with a dedicated helper row, but otherwise you are going to have to manually update the range every week.

Is it possible that future weeks could be left blank or as zero until they are needed? How is your sheet populated?

TWHT

Hi,

I have the whole year populated sadly, as I need to be able to know where techs will be for each week.

The idea is to have a cell in each project row that tracks expenditure to-date, for each project. There's then a budget cell that has conditional formatting so that it changes to red and sends out an email if the expenditure hits 70% of the budget. That expenditure is pretty much calculated in advance based on the number of techs on a project per week.

Everything works, it would just be nice if the expenditure tracking updates automatically, as the sheet has the means to do so, just not the tool it would seem :(

Thanks

Paul Newcome

Are you able to provide a screenshot for context (sample data is fine)? I wonder if there may be some other way to work through this.

TWHT

Sure, let me know if these illustrate the issue, or whether you'd like more screenshots:

Overview:

The formula:

The version with the index idea that failed:

Thanks for your help!

TWHT

Would it be possible for an automation for example to be triggered by a change in Current Week, and then apply the week number to all the formulas in the PO Balance?

TWHT

I think this may have solved it.

Paul Newcome

Yes. That will work, but it is certainly a bit on the clunky side. In that top light blue row that has [Current Week]2 in it… Can you enter the week numbers in the rest of the columns? If so, you could use this instead of the monster nested IF…

=SUMIFS([Week 24]@row:[Week 52]@row, [Week 24]$2:[Week 52]$2, @cell <= [Current week]$2)