I've spent hours pouring through posts using VBA, VLOOKUP, INDIRECT, etc. but haven't had much luck for this specific situation and just ended up making myself more confused.
I'm essentially doing a daily financial report. I have two worksheets. The first is "Summary" and simply gathers all the data and numbers from the day's sales and presents them neatly. The second worksheet is "Weekly" and this is where I'll input direct values into cells every day (e.g. Items Sold, Retail Shipping Cost, Total F&B Sales today).The "Weekly" worksheet has each date of the month in columns so, for instance, all the numbers for the 3rd of whatever month are input manually by myself under Column D. All the numbers for the 4th of the month are input into Column E.
The first worksheet is essentially just listing these values again more neatly or occasionally totaled up into larger sales categories which leaves me with a long column of cells in "Summary" containing formulas like "Weekly!F8" "Weekly!F10-Weekly!F3" that I have to change every day to reference the next date's numbers (e.g. Weekly!G8" "Weekly!G10-G3"). I've been using Search and Replace so far to search for "Weekly!*" and replace with "Weekly!next letter of alphabet" but I'm looking for a different solution.
Summary - https://i.sstatic.net/5ANFk.png Weekly - https://i.sstatic.net/6OysS.png
Is there a way to automate this so that the cell formulas change to reference the following column in another worksheet depending either on the date or a reference cell that contains an integer denoting the day of the month. I could change that one reference cell value manually and ideally have the whole column of reference formulas update as well.
You can use the OFFSET() worksheet function. For example in your first image, the highlighted cell's formula will become =+OFFSET(Weekly!A10,0,$A$1) where cell $A$1 is the reference cell that contains the day of the month.
A better approach than using OFFSET() in all your formulas is to add a column at the end of your "Weekly" worksheet and call it "Today" for example. Use the OFFSET() worksheet function in all cells of this column. Then you can reference only this column in your "Summary" sheet.
Example: In your "Weekly" worksheet use "AG" column as "Today"'s column. Then the formula of "AG10" will become =OFFSET(A10,0,$A$1) and the formula of the highlighted cell in your first image will simply become =+Weekly!AG10
