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