Formulas creating workbook references

[raz1337]

Hiya! Thought I'd post this here instead of banging my head against the wall for the next few hours.

Okay, I have budget spreadsheets I've created to span 2 week time frames for myself. They're named for their 2 week periods, i.e. "10-10 to 10-23.xlsx". I have a template file I just use for each new period.

What I'm trying to do is have each one have a cell that returns an amount from a cell from the spreadsheet of the last 2 week period. At first, I made a formula to create the name of the last period's filename in a cell. My earlier versions landed me with serial numbers. I ended up with this:

Please Login or Register  to view this content.

The daily expenses worksheet consists of the dates of each day in the 2 week period. So, the formula above just takes the first and last dates, subtracts 14, and adds a .xlsx to it. Now, I want to take that and use it to reference cell H39 in the Daily Expenses worksheet of that workbook.

Maybe I was just going about it the wrong way. I tried the most obvious way and failed:

Please Login or Register  to view this content.

I can get the file path right with this:

Please Login or Register  to view this content.

But then doing a workbook reference with it just...doesn't like it. I tried defining a formula with the first quoted formula. The end result was missing the day in the filename.

Is there a way to do this? I just tried guessing at it. UGH! I think I'm coming to the conclusion that you can't use formulas to derive where the workbook reference is. Maybe I'm missing something.

[sglife]

You need to learn indirect() first

Please Login or Register  to view this content.

[raz1337]

I looked up Indirect() and it says that if it references another workbook, then that workbook has to be open. It returns a #REF error.