Adjust value depending on month selected in sheet

[jason7579]

I am trying to create a master document that itemizes all of the stationery ordered from a branch within a particular month.

I currently have a spreadsheet for each branch that lists the product code, description and quantity for all stationery. Each branch has exactly the same information (except for quantity) and the spreadsheet is split into tabs for each of the 12 months.

Instead of having 12 master sheets (one per month) and referencing to every cell I would like to keep one master sheet in which I can enter the month name on the first page so that the cells update accordingly.

So basically instead of the following:
='\\SHAREPOINT LOCATION\[Stationery Branchname 2017 18.xls]April'!C2

I want the text April to refer to a cell E2 where I will manually input the month name as desired.

Is this possible? (Sorry for any confusion, I find it hard to explain exactly what is needed).

Thanks
Jason

 

[JackDanIce]

Try adjusting:

Dim strPath as String

strPath = "='\\SHAREPOINT LOCATION\[Stationery Branchname 2017 18.xls]@month'!C2"
strPath = Replace(strPath, "@month", cells(2, 5).Value)

 

[jason7579]

Thanks for this, any chance you could give me a little guidance on how to use it. I am fine putting formulas into cells but I struggle a little when it comes to vbasic etc.

 

[JackDanIce]

My mis-understanding, thought you wanted code. As formula, try:

=INDIRECT("'\\SHAREPOINT LOCATION\[Stationery Branchname 2017 18.xls]"&$E$5&"'!C2")

 

[jason7579]

Thank you very much for your help. Unfortunately this only appears to be working when both workbooks are open, when the document saved in the SharePoint location is closed I get a #REF ! error in the destination sheet.

I have tried to look into this a little further and by all accounts it seems that VBA is the only way to get exactly what I am looking for. It is about time that I learnt a bit more and expanded my knowledge of VBA in excel so this is a good starting point.

 

[JackDanIce]

Good luck and yes I do not believe Excel worksheets can read data from closed workbooks