I want my spreadsheet to be easily replicated with each new year. I've already got an input section that allows me to enter the present year (for instance 2014 in A1), generate the prior year (present year - 1, which would be 2013 in A2), and I have created a third cell (A3) which creates this file's name by combining "ST" with the year in A2 (in this case would be return ST2013). The complicating factor is the file sits on OneDrive, so I have to provide the path to find it there, which I've created in A5. A6 is a CONCATENATE with A5 and A4.
This should have constructed the complete path I need for the cell I'm trying to reference.
Summarizing my data:
A1: 2014 (this is a number input)
A2: 2013 (=A1-1)
A3: ST2013.xlsx ("ST"&A2&".xlsx")
A4: [ST2013.xlsx]'P13'!$BS$5 (CONCATENATE("[",A3,"]'P13'!$BS$5")"
A5: https://d.docs.live.net/Users/etc, etc, etc (this is a text string input)
A6: https://d.docs.live.net/Users/ . . . /[ST2014.xlsx]'P13'!$BS$5
I need help constructing the string. Here are the pieces I need: A5 (the location), (the file), the worksheet (called "P13") and the cell reference (BS5) (all of which are contained in A4).
I've tried just doing a direct link to the workbook with the date to which I'm trying to reference (which does work, tho even when it's reaching the file on OneDrive, since it's open, it just references the file's name, no path), then replacing the reference with an INDIRECT string with A5 & A4, but then get a REF, even when the file is open (I know INDIRECT requires the file to be open to work).
Seems like this should be simple, just leveraging INDIRECT, but it's cumbersome--maybe there's a more facile function than INDIRECT? Do I have to reference the file on my hard drive rather than OneDrive (which would defeat the purpose of OneDrive!). Appreciate any help. Thank you!
