Hi,
I have what I thought was a simple problem but having googled at length I have been unable to find a simple solution and am grateful for any help anyone can offer.
Each member of staff has a work book and in the work book are work sheets with the names of the months of the year. I can not have all of these workbooks open.
There is a team leader work book with a link to a particular cell, lets call it D10, in each of the staff work books and returns its value.
The difficulty is that the path to cell D10 has to change each month, as we are only interested in the current month's worksheet.
So, my current understanding is that this requires VBA, which is not an option. I've been playing with concatenate to simply create the path and then paste it myself but I have to paste the results of my formula into notepad and then back into excel to make it execute the function, which seems perverse.
What I want to do is enter the name of the month required in cell A1 in my team leader work book and then all the links would update for that month.
For further illustration, my concatenate work around function is as follows:
=CONCATENATE("='C:\Users\Doug\Desktop\WA Spreadsheets\[Doug board.xlsx]",$A$1,"'!$D$10")
Thanks in advance,
Doug
I have what I thought was a simple problem but having googled at length I have been unable to find a simple solution and am grateful for any help anyone can offer.
Each member of staff has a work book and in the work book are work sheets with the names of the months of the year. I can not have all of these workbooks open.
There is a team leader work book with a link to a particular cell, lets call it D10, in each of the staff work books and returns its value.
The difficulty is that the path to cell D10 has to change each month, as we are only interested in the current month's worksheet.
So, my current understanding is that this requires VBA, which is not an option. I've been playing with concatenate to simply create the path and then paste it myself but I have to paste the results of my formula into notepad and then back into excel to make it execute the function, which seems perverse.
What I want to do is enter the name of the month required in cell A1 in my team leader work book and then all the links would update for that month.
For further illustration, my concatenate work around function is as follows:
=CONCATENATE("='C:\Users\Doug\Desktop\WA Spreadsheets\[Doug board.xlsx]",$A$1,"'!$D$10")
Thanks in advance,
Doug
