Mar 30 2021 02:50 PM
I am having issues with this document again. I am not used to working in Excel, have very limited knowledge of Excel and I am not real familiar with the terminology used within Excel, so please take this into consideration when and if you are gracious enough to respond.
The document attached was created by the previous admin. I copied the whole document, from an old job that was completed in September, into a new folder which started a new job that we began in November.
I take information that is given to me and enter into the document. There is information on the top of the page and the bottom of the page that I am having issues with. I add these numbers from one week to the following week and continue to the following week, etc. and the numbers total each week, taking totals from the beginning of the document (week 1, Nov 30-Dec 6) ) to the last page of the document, which will be approximately (week ?, Sept 20-26) .
Each week when I copy and paste a new page into the document, I have to go in and re-enter the formula for each cell because it is not carrying over the formula from the previous week.
I was told to just copy and pasted the document and the formulas would transfer over. I have copied and pasted by highlighting the content from one page and then going to the next page and highlighting and pasting. I have also, clicked ctrl and drag the document to the right on the bottom toolbar. I hope I am making sense in explaining my dilemma.
Please help and thank you for your assistance!!
Terrie
Mar 30 2021 10:35 PM
Solution@sumrall90 Oh my! what an awful workbook. The designer made the classic mistake of trying to combine capturing data and making a summary report in the same sheet. But since it seems to fulfil the needs of the organisation, I made a few adjustments to the last sheet. All formulae that refer the the previous week's sheet are now fixed.
Next week (Apr 5-12) you can create a sheet for the new week, like you always have done. The formulae will NOT automatically "roll-forward" one week. They will still point to sheet "Mar 22-28". But, no panic. You can use Find and Replace to update the references in one go. No need to re-enter each formula one-by-one. Select the entire sheet for the new week. Press Ctrl-H. A Find and Replace window opens. In the Find box type Mar 22-28. In the Replace box you type Mar 29-Apr 4. Replace All. Now, all references to the 'old previous week' have been updated to the 'current previous week'. Do the same thing every week from now on. Find the name of the old reference (like Mar 29-Apr 4) and replace it by the new reference (like Apr 5-12).
Mar 31 2021 06:11 AM
Mar 31 2021 08:19 AM
Mar 31 2021 08:45 AM
@sumrall90 Well the first obvious suggestion is to be absolutely sure that you typed the sheet names names exactly as you have them. That is, no quotation marks or spaces that aren't in the actual sheet name. Tried it my self on the file you uploaded. Worked perfectly.
Apr 01 2021 05:56 AM
Mar 30 2021 10:35 PM
Solution@sumrall90 Oh my! what an awful workbook. The designer made the classic mistake of trying to combine capturing data and making a summary report in the same sheet. But since it seems to fulfil the needs of the organisation, I made a few adjustments to the last sheet. All formulae that refer the the previous week's sheet are now fixed.
Next week (Apr 5-12) you can create a sheet for the new week, like you always have done. The formulae will NOT automatically "roll-forward" one week. They will still point to sheet "Mar 22-28". But, no panic. You can use Find and Replace to update the references in one go. No need to re-enter each formula one-by-one. Select the entire sheet for the new week. Press Ctrl-H. A Find and Replace window opens. In the Find box type Mar 22-28. In the Replace box you type Mar 29-Apr 4. Replace All. Now, all references to the 'old previous week' have been updated to the 'current previous week'. Do the same thing every week from now on. Find the name of the old reference (like Mar 29-Apr 4) and replace it by the new reference (like Apr 5-12).