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:

Depot	Category	Type	2019-01	2019-02	2019-03	2019-04
Depot1	Count	Planned	1	0	5	4
Depot1	Turnaround	Planned	2	0	2	1
Depot1	Cost	Planned	£20	£0	£250	£250
Depot1	Count	Unplanned	3	5	2	5
Depot1	Turnaround	Unplanned	1	2	7	2
Depot1	Cost	Unplanned	£400	£500	$700	£200

<tbody>
</tbody>

The table that's calculating the values:

Depot	Type	Count	Turnaround	Cost
Depot1	Planned	=FORMULA	=FORMULA	=FORMULA
Depot1	Unplanned
Depot2	Planned
Depot2	Unplanned
Depot3	Planned
Depot3	Unplanned

<tbody>
</tbody>

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)