Need to keep historical data on one sheet from another that changes weekly

[vcastro458]

Hello everyone,
I'm trying to find a way to keep historical data from one worksheet (named Staffing summary) on another (named Historical). The problem is, the data on the 'Staffing summary' sheet will change every friday. So when I change the date on the 'Staffing summary' sheet (cell S1), my data on the 'Historical' sheet for the previous dates disappears. I need it to stay put and record the new data on the next row.

I've only entered formulas on 'Historical' sheet on a few cells (B5-E5 and B6-E6) because there are a lot of formulas to enter and I did not want to spend a ton of time entering formulas; if I will not be able to retain the historical data I need.

So, most of the information showing right now on 'Historical' sheet is pulled from 'Staffing summary' sheet with the date on cell S1 as 1/18/19, but when I change that date to 1/25/19, the data goes to the new row and deletes from the previous row.

I do not know how to do codes or VBA's or anything more complicated that the formulas you see on any of the tabs of the workbook attached. So if there is a simple solution, I will probably be able to follow your suggestions.

Thank you in advance for any assistance or suggestions.

Vanessa
PS.: You will see some ref errors on the 'Staffing summary' sheet, my attachment was too large, I deleted a few tabls to be able to attach it.

[FDibbins]

There is no formula way to do what you want. Any formulas would reference the cells that change each week, and would therefor show the latest data.

The only ways around this, that I can see, would be to use VBA, or to add each new week's data below last week's. If you added a column for data, then we could probably come up with some formulas that would test the (long-and-growing) list, to pull into the summary you have.

On a side note, this formula will pull the info you want, based on the date and the City (?), and can me copied across (and maybe down) as needed. You will just have to change the reference to the City, when that changes (bolded)...
=INDEX('STAFFING SUMMARY'!$B:$M,MATCH(HISTORICAL!B$2,'STAFFING SUMMARY'!$A:$A,0),MATCH(HISTORICAL!$B1,'STAFFING SUMMARY'!$B$1:$M$1,0))

[vcastro458]

FDibbins, thanks for the info.
I really don't understand VBA, I'm still trying to learn the basics.
For now, I came up with a temporary solution.
I created on a row with the formulas that pulls the info from the 'Staffing Summary' sheet, then I just copy/paste values only on the next row, the following week, I repeat the process, thus creating the historical tracking I need. It's not exactly what I wanted, but it's still a fairly simple way to get the info I need.
I will keep working on it.
Again, thanks for trying to help.
Vanessa