Forum Discussion
Created a table and trying to link to to other table in the workbook
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
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'
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.
I was hoping to enter the cell #(C1) into the formula, since the cell was create as a list by using data-validation.
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
Try replacing
WeeklyAvgs[[W/E 1/13]:[W/E 1/5]]
with
INDIRECT("WeeklyAvgs[[&$C$1&"]:["&$C$2&"]]")
- What is the meaning of the use of the &?
& concatenates text strings. For example "A" & "B" returns "AB".
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?
Would it be very unpleasant to update the cells for the previous week manually?
