Fields updating based on the date

[paulmoss]

We have a spreadsheet that our Transport manager uses to manage a rota of delivery runs and which of our drivers will be doing them an example of this is attached as Rota.xlsx Rota.xlsx

What we are trying to achieve is having a 2nd spreadsheet that shows the rotas for the current and the next week. See attached Summary.xlsx Summary.xlsx

When looking at the spreadsheet the date in cell A1 will update to the start date of the week you are in. Then we would like the data in the rest of the table to update from the rota spreadsheet. The second table should show the rota for the following week, so will need to use the date in A9 to update the rest of the table from the Rota sheet.

We are trying to automate this process so that the Transport manager only updates the rota spreadsheet and the summary sheet automatically updates. I hope this makes sense, if not feel free to ask questions and I will try to answer them to the best of my ability.

[HansDouwe]

Please remove the stuctured table from the main sheet and empty all cells in Summary and

try in cell A1:

Please Login or Register  to view this content.

[paulmoss]

Thanks that formula worked great. Do you know if it will work it both files are in OneDrive/Sharepoint?

[HansDouwe]

Thanks for your feedback. Glad to have helped.

I don't know how Onedrive/Sharepoint is organized or where the connection between the two workbooks can be made.
You should try.

Please consider adding reputation to any helpers you think they deserve.
In that case please click * Add Reputation left below their answers.

[paulmoss]

Once again thanks for the formula. However, the IF part of the formula doesn't seem to be working now. From what I can gather it doesn't recognise "t". Any suggestions on how I can resolve this?

[JeteMc]

The LET function is not supported by the 2016 version of Excel.
I believe that the formula would have to be written:

Please Login or Register  to view this content.

[paulmoss]

Thanks for the formula JeteMC. We are using Excel 365 not Excel 2016, I just forgot to update my profile, for which I apologise.

[HansDouwe]

If you are using Excel 365 then the formula from post #2 should also work.