Structured References Question

[mlbrenes]

Hi,

Is it possible to change a specific value in a structured reference calculation?

Basically I want to change the week depending on week I select from a dropdown in another cell.

My table has a column per week for the value I want to obtain, so I want the Sum Range to move depending on that week that I selected, I hope I'm making sense!

My formula is:

=SUMIFS(TM_Table[[#All],[[COLOR=#ff0000][B]2019-04[/B][/COLOR]]],TM_Table[[#All],[Depot]],[@Depot],TM_Table[[#All],[Category]],$CB$4,TM_Table[[#All],[Type]],[@[Planned/Unplanned]])

What I want to change is the [WEEK] to get the value in column BY3

And I have 2 tables, TM_Table:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Depot[/TD]
[TD]Category[/TD]
[TD]Type[/TD]
[TD]2019-01[/TD]
[TD]2019-02[/TD]
[TD]2019-03[/TD]
[TD]2019-04[/TD]
[/TR]
[TR]
[TD]Depot1[/TD]
[TD]Count[/TD]
[TD]Planned[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Depot1[/TD]
[TD]Turnaround[/TD]
[TD]Planned[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Depot1[/TD]
[TD]Cost[/TD]
[TD]Planned[/TD]
[TD]£20[/TD]
[TD]£0[/TD]
[TD]£250[/TD]
[TD]£250[/TD]
[/TR]
[TR]
[TD]Depot1[/TD]
[TD]Count[/TD]
[TD]Unplanned[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Depot1[/TD]
[TD]Turnaround[/TD]
[TD]Unplanned[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Depot1[/TD]
[TD]Cost[/TD]
[TD]Unplanned[/TD]
[TD]£400[/TD]
[TD]£500[/TD]
[TD]$700[/TD]
[TD]£200[/TD]
[/TR]
</tbody>[/TABLE]


The table that's calculating the values:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Depot[/TD]
[TD]Type[/TD]
[TD]Count[/TD]
[TD]Turnaround[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Depot1[/TD]
[TD]Planned[/TD]
[TD]=FORMULA[/TD]
[TD]=FORMULA[/TD]
[TD]=FORMULA[/TD]
[/TR]
[TR]
[TD]Depot1[/TD]
[TD]Unplanned[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Depot2[/TD]
[TD]Planned[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Depot2[/TD]
[TD]Unplanned[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Depot3[/TD]
[TD]Planned[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Depot3[/TD]
[TD]Unplanned[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I appreciate your help!! I'm pretty desperate.

Thank you!

 

[RoryA]

You could use INDIRECT and pass the column reference as a string, or use INDEX and match to return the relevant column based on the header?

 

[mlbrenes]

I figured it out

Used a HLOOKUP, in case you're interested this is the formula I used:

=HLOOKUP(Week,TM_Table[[#All],[2018-42]:[2019-52]],VLOOKUP(CONCATENATE([@Depot],"-",CB$4,"-",[@[Planned/Unplanned]]),TM_Table[[#All],[Concatenate]:[Value]],2,FALSE),FALSE)