How do i advance the date of a cell by one week moving across multiple worksheets?

Hello group,

To summarize the issue I have the date in cell b3 on every sheet. I want the date on the next sheet IE: 2002 to be one week later. So lets say sheet 2001 b3 is the date of 12/21/19 I want sheet 2002 cell b3 to be 12/28/19 and so on for all the sheets.

If anyone can provide the formula that I need to be able to advance the date of a cell by one week from one work sheet to another it would be greatly appreciated. I have a workbook that I use for tracking certain statistics for work. Last year I built this workbook out for one year. Now its time to add on to this workbook, however there seems to have been some drastic changes that have happened in excel that is not allowing it to copy & paste and track week to week as it did when I built it.

I have tried everything i can think of as well as many tricks from different sites, videos, and even some of the threads on here to no avail. I have looked back at the other sheets and can not see why it won't continue to track properly. I would hate to have to bomb a years worth of data just to move forward.

If it helps any i am using office 365 and it is installed on my machine, not a web version.

Hi there, if you enter this formula in cell B3 on sheet "2002" it should do the trick: ='2001'!B3+7

Good luck
Louis

Louis,

That will work but only for the local sheet.

If I try to copy and paste that formula to multiple sheets by selecting them at the bottom of the workbook I will only get ='2001'!b3+7 on sheets 2003 thru the end of eternity. For some reason the '2001' will not update to 2002 2003 ect.and be one under the current sheet. So if I'm on sheet 2005 the "2001" should be 2004.

Something has happened in excel that no longer allows a sheet to look back and update the each current sheet based off of the previous.

I thought I had lost my mind but I've opened the master copy of the workbook I did last year and can not copy and paste the last sheet to a new sheet and have it track in the same workbook as it did.

Eric

Ok, sorry about that. Try this one, however it ONLY works with numerical sheet names. formula: =INDIRECT(MID(CELL("filename",A2),FIND("]",CELL("filename",A2))+1,255)-1&"!"&"B3")

Good Luck

That one seems to work!!!!!

However I had to add a +7 to the very end of it to get it to track properly.

It will only track for 1 year. Since this has decided to be such a headache, I decided to run this work book out a few years. it will not track past week 52 of 2020.

I believe I have the cell tracking down that needs to happen week to week, until the time that a new year happens.
This can be seen in the workbook as sheet 2001 has to have manually entered references back to sheet 1952, for cells B3,and merged cells B4&5 thru G4&5.

Now I have the issue of tracking data from each weekly sheet such as 2001, 2002, and 2003 back to sheet mcp.
I have this sheet for data that I have to compare differently from my week to week data.

For sheet 'mcp' ROW 5 columns E, F, G, H, I've used this formula =SUM('1951'!C9:C14,'1952'!C8:C14,'2001'!C8)to extract the number from my weekly sheet.
Column F on mcp will reference I on the weekly sheet.
Column G on mcp will reference G on the weekly sheet.
Column H on mcp will reference H on the weekly sheet.

What I need to accomplish when I drag to fill or copy & paste down on sheet 'mcp' that ROW 6 formulas would read as this =SUM('2001'!C9:C14,'2002'!C8:C14,'2003'!C8)
Row 7 would then be =SUM('2003'!C9:C14,'2004'!C8:C14,'2005'!C8) and so forth for each column as mentioned above. Right now when I drag to fill, my sheet numbers stay
the same but my cell range increases by 1.

For what it is worth, I have made up copy of what I want to happen.

On the mcp Sheet in column L thru W I have typed out what I would like to see combined to create the sum function I want in column E. I could then do the same for the
other 3 columns and achieve what I want.........if this is possible.

Eric

I was able to get the columns to combine using =concat now what would bet the trick to get that singe cell to become part of a =SUM function
??

Sorry Eric, i'm not quite following what you're trying to accomplish now?

Louis,

Its ok if I'm confusing you, because I'm beyond confused myself. I have attached again what I've been able to accomplish. However now it is as if the sheet has a mind
of its own. I was able to copy 2 of the R column cells and add =SUM( ) and get the end result I want........ however now on the 3rd row down its comming back with the
REF Error.

Please take a look at the mcp sheet and you can see what I have accomplished.

I basically want all of the text data in column R to become a =SUM formula in column E in the same row

Thanks Eric

Ok well the good news is I have my sheet working and tracking now!! The biggest headache I've ever had.
Suffice to say the easiest way to do this is Build the formula in cells as I did. make sure you make a text formatted column that you can type in first =SUM(
Then use the =CONCAT formula to condense it all down to one cell. Highlight the entire column and copy and paste into word. Then re-copy all of what you just pasted into word and paste that into the first cell you want the formula in and excel will sort it out from there.

I have no idea why this works but it did. Now I will replicate this for the other 3 columns I need and be finished. Building this in cells to the side allows the functions of rapid fill and population of the cells just the way I wanted. Its only a couple extra steps of C&P, instead of typing out each formula in each cell.

Thank you all for your help!