Forum Discussion
Date/Data referencing
I am building a spreadsheet for work. My first sheet is an overview and my second sheet is my user input data. The idea is for me to input data everyday into the sheet (each day has a different row) and it automatically references that data in the overview page. I have made it reference the data for day 1 but I would like to be able to change the date on the overview page and have it automatically reference the date for that corresponding date on the input sheet.
3 Replies
Hello jdauzat,
You can make your Overview sheet automatically pull data for any date using XLOOKUP (Excel 365/2021) or INDEX/MATCH (all versions).
Assume your input sheet is named InputData, column A has dates, columns B, C, D… have data, and cell B1 on Overview has the date to look up.
For XLOOKUP use: =XLOOKUP(B1, InputData!A:A, InputData!B:D, "Not Found").
For INDEX/MATCH use: =INDEX(InputData!B:Z, MATCH($B$1, InputData!A:A, 0), COLUMN()-COLUMN($B$2)+1) and drag horizontally for multiple columns.
Changing the date in B1 will automatically update all referenced data. This works for daily dashboards where each row in InputData is a different date.Here you will get an idea to collect information from other sheet to dashboard sheet using formulas.
With that limited information it is very difficult. Attaching a sample workbook or uploading it to a cloud share like sharepoint would be best or at least giving specific examples. for example you don't even include your current formula.
that all said, I first want to commend you on your set up as it sounds like it is well done (have single source data and separate presentation/overview page)
it sounds like you need to use XLOOKUP or FILTER functions
