Created a table and trying to link to to other table in the workbook

Copper Contributor
I am having excel formula/function writers block!!!  I am a student trying to work on a spreadsheet that has a "Data" tab of inputted weekly averages (table 'WeeklyAvgs').  The stump that I am having is on the "Summary" tab.
Table: WeeklyAvgs
Florida710_1-1705434298371.png

 

 
I am trying to automate the 'Summary' tab.  The Summary tab as multiple tables, based on period look back, e.g. YTD, 12 week, Previous 12 week, Week ending, and previous week ending.
'Summary Tab'
Florida710_2-1705434430633.png

 

 
Currently, I am using the xlookup in the various tables and am manually going in to update the appropriate columns(week ending columns) for the summary tab periods to update their information - The tables are calculating correctly.  The problem occurs when other team members go in and update with the formula to change the table columns in the xlookup  - surprise, surprise.  I would like to be able to use adropdown to change the w/e dates(from/to) from the table 'WeeklyAvgs', which would dynamically update the info in the table.
 
Florida710_0-1705434218234.png

 

I was hoping to enter the cell #(C1) into the formula, since the cell was create as a list by using data-validation.

 

Florida710_4-1705434882976.png

 

 
With the previous period table(Summary tab), I was using the offset function but was having an issue of incorporating the xlookup function to work correctly with stepping back a cell of the current period selection.  I think I am overthinking it, but everything I have tried is not working,
 
I will appreciate any and all help with a solution.  
7 Replies

@Florida710 

Try replacing

WeeklyAvgs[[W/E 1/13]:[W/E 1/5]]

 

with

 

INDIRECT("WeeklyAvgs[[&$C$1&"]:["&$C$2&"]]")

@HansVogelaar 

 

Thank you!  That has it working.  To get the previous week to automatically update, would I use the OFFSET function to step it back a cell on the table??  Or, do I have that wrong?

 

Florida710_0-1705441372788.png

 

What is the meaning of the use of the &?

@Florida710 

Would it be very unpleasant to update the cells for the previous week manually?

@Florida710 

& concatenates text strings. For example "A" & "B" returns "AB".

Its not necessarily unpleasant, but onehas to manually update the YTD, 12 week, previous 12 week, weekending, and previous weekending - 5 manual steps. I would just like the previous 12 week and weekending to auto populate based off the updated w/e period.

In a perfect world, I would like to just input the new weekending data and the 12week and YTD would autoadjust respectively.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?