Forum Discussion
Excel Document Issue - Laptop
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).
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).
- Thank you so much Riny van Eekelen! I am going to save this document again in a different location so I can work on it and not mess us the issues you fixed and then try what you told me to do for the following weeks! I appreciate your help!!
- Well, I have an error message that comes up when I try this. "We couldn't find anything to replace. Click options for more ways to search." "FYI: It's possible the data you're trying to replace is in a protected sheet. Excel can't replace date in protected sheets."
I am sure I am not doing something correct. Any suggestions or fixes for me to correct what I am doing wrong?
Thank you!!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.
