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:
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:
I can get the file path right with this:
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.
Bookmarks