# Structured References Question

#### mlbrenes

##### New Member
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:

Code:
=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!

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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?

Last edited:
I figured it out

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

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

Replies
1
Views
141
Replies
0
Views
143
Replies
1
Views
658
Replies
2
Views
234
Replies
0
Views
387

1,203,727
Messages
6,056,970
Members
444,899
Latest member
Excel_Temp

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back