Hi. Is there a way to have indirect file addressing? As an example: I have several spreadsheet files (one per month) with info in. I want to produce a (yearly) summary file. So, there are twelve rows accessing the info in each relevant file. This I can do manually, but I want to speed things up by making the file location part an address that is constructed by the cell’s location. i.e. I can concatenate the ‘file:/// …’ + ‘01’ or ‘02’, etc. but I can’t figure out a way to put that in a cell to get it to use the file location text to reference the file. (Not sure I’m making sense, as I’m writing this in a bit of a hurry - sorry.)
Yes, and it is called INDIRECT, but try to avoid referencing named ranges. (You can “assemble” the string yourself.)
You can use somesting like &ROW(B6)+4 for the numbering part.
PS: For me it makes more sense to have all data in one sheet and use filter etc to display only parts. But that was not your question.