Update file paths when the week changes

[zeyo]

Hi All - my first post!

Ok my situation is that I am looking up values from another workbook. There is a new workbook every week with updated data. What I want to do is have my file path automatically update when I change the week number.

For example: I have the week in cell A1, and a lookup in cell A3 with the following formula: 'K:\reports\Week 30.xlsx]Sales'!A1

At the moment it is looking up week 30, next week when i change cell A1 to week 31 i want the file path to update to say: 'K:\reports\Week 31.xlsx]Sales'!A1
Is this possible?

[6StringJazzer]

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:

Please Login or Register  to view this content.

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:

1. Under the text box where you type your reply click the Go Advanced button.
2. In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
3. In the pop-up, click Add then Select to select a file from your local drive
4. Click the Upload button to upload the file
5. 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.