I can think of two ways to do this. The first way is to use the INDIRECT function to build your formula. If your week number is in A1 of the same sheet as this formula, and you want to refer to Sales!A1 in the other workbook, it would look like this:
However, the problem with this is that Excel will require the other workbook to be open, or it will give you an error for this formula. That is a consequence of INDIRECT.
The other way is to write a macro that updates all your formulas every time the week number in A1 changes. If you are the only one using this file then that's probably workable, but otherwise you would have to make sure that anyone else using it allows macros to run.
I can help with the macro if I can see your actual file. To post a file:
- Under the text box where you type your reply click the Go Advanced button.
- In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
- In the pop-up, click Add then Select to select a file from your local drive
- Click the Upload button to upload the file
- Click Done to attach it.
It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.
Bookmarks