Hi!
I am a new member of Excel forum, but I'm really hoping to get some help/guidance on what seems to be an impossible problem...
I have a masterfile (of sorts) where I wish to retrieve budget amounts from different excel workbooks located on a webpage, in order to get a good overview of all the budgets. Every workbook is a specialized function in a project with their own budget etc. Every workbook has the same layout and has around 20 sheets (with the same worksheet names).
Currently I am able to link to the excel workbook I want, and retrieve a cell value if I specify the workbook, worksheet and cell number, using the following syntax (example):
='https://testpage.com/documents/[mybook.xlsx]sheet1'!A1
However, mybook in the example above contains different sheets, sorted by month: 1501, 1502, 1503, etc, each with a monthly budget.
I want to be able to change the sheet-reference above depending on what month I'm currently in, without having to change the formula (the workbook references are not going to change). I.e: In my master file i want to have a cell where the user can write in 1501, 1502, etc, which my formula should use as sheet-input, instead of having to rewrite the formula/link and specify every time.
My idea so far is trying to use CONCATATE or something similar. For example, in my masterfile I will have following cells, if I want to retrieve the value in cell A1 in worksheet 1501:
E2: 1501 ((manually put in by user!))
E3: =CONCATATE("'https://testpage.com/documents/[mybook.xlsx]";E2;"'!A1")
This returns the file path that I want:
https://testpage.com/documents/[mybook.xlsx]1501'!A1
Then comes the problem of using this file path as a reference/link path. I cannot simply write =E3 in cell B14 in my masterfile, since it would only return the texstring "https://testpage.com/documents/[mybook.xlsx]1501'!A1". It will not return the value A1 of worksheet 1501 on the external webpage, which is what I want.
I have thought of trying to use INDIRECT(), but that doesn't work with closed workbook. But I seriously running low on ideas....
I do not know how to use Macros unfortunately, and don't want to use add-ins since this file is going to be used by different users (with very very low excel knowledge).
If someone knows how to solve this problem of mine I would be forever in your debt!!
Best regards,
Anna
Bookmarks