I have a workbook (Banking) that has the following data in it and it is created from a template,. It basically wants to get data from other workbooks without open them.
Column A
Col A2-A53 lists the day of the week (this will always be a Saturday) by looking at B2.
Column B, formatted as Custom, dd/mmm/yy;
B2 is always the last Saturday of the previous year. I use the formula =EOMONTH(B2,-1)+1+MOD((7-WEEKDAY(EOMONTH(B2,-1)+1,11)),7) to get the First Sunday of a year and subtract 1 from it to give the previous Saturday.
B3 adds 7 to B2 to get the next Saturday using the formula =B2+7. This is then copied down to B53.
Column C and D, have formulas that do not need attention.
Column G onwards no issues.
Columns E, formatted as Currency
This is where I have the problem. There is a formula in E2-E53 that is as follows:-
Columns F, formatted as Currency
As column E, this is where I have the same problem as Column E. It wants data from mcell E33 rather than Q65 as in colum E.
All the files being referenced by the above formulas are in the same folder as the Banking workbook.
The problem I am faced with every year is that the sheet names in the above will change as the first Sunday and subsequent Sundays will change for each month.
As an example, for January 2019, the formula would have read:-
Whereas for January 2020, the formula would have read:-
Whereas for January 2021, the formula would have read:-
I have tried to use the & function to create a formula along the lines:-
Q2 is path using the formula =LEFT(CELL("filename"),FIND("[",CELL("filename"))-0). This leaves the [ bracket in
T2 is =TEXT(B2,"MM") to build the month number
U2 is =TEXT(B2,"mmmm") to build the full month name
S2 is =TEXT(B2+1,"dd-mmm-yy"), otherwise it inserts a numerical value after the month name. The b2+1 will return the correct name
The $Q$65 is the required cell value from the cell.
Having built up the formula in P2, I then refer to it in E2. All it does is look as though I have typed in text, doesn’t look like a formula. When I open Banking, it doesn’t offer to Update links.
I attach a copy of the workbook in question.
What is the way forward to automate this?
Column A
Col A2-A53 lists the day of the week (this will always be a Saturday) by looking at B2.
Column B, formatted as Custom, dd/mmm/yy;
B2 is always the last Saturday of the previous year. I use the formula =EOMONTH(B2,-1)+1+MOD((7-WEEKDAY(EOMONTH(B2,-1)+1,11)),7) to get the First Sunday of a year and subtract 1 from it to give the previous Saturday.
B3 adds 7 to B2 to get the next Saturday using the formula =B2+7. This is then copied down to B53.
Column C and D, have formulas that do not need attention.
Column G onwards no issues.
Columns E, formatted as Currency
This is where I have the problem. There is a formula in E2-E53 that is as follows:-
Rich (BB code):
='full path\[nn full month name.xlsx]sheet name'!$Q$65
Full path is the location of where this workbook (Banking) is.
Nn is 01 to 12, full month name is January, February, etc. Sheet Name 05-Jan-20, 12-Jan-20 etc.
='Z:\2020\[01 January.xlsx]05-Jan'!$Q$65Columns F, formatted as Currency
As column E, this is where I have the same problem as Column E. It wants data from mcell E33 rather than Q65 as in colum E.
All the files being referenced by the above formulas are in the same folder as the Banking workbook.
The problem I am faced with every year is that the sheet names in the above will change as the first Sunday and subsequent Sundays will change for each month.
As an example, for January 2019, the formula would have read:-
Rich (BB code):
='Z:\2019\[01 January.xlsx]06-Jan'!$Q$65Whereas for January 2020, the formula would have read:-
Rich (BB code):
='Z:\2020\[01 January.xlsx]05-Jan'!$Q$65Whereas for January 2021, the formula would have read:-
Rich (BB code):
='Z:\2021\[01 January.xlsx]03-Jan'!$Q$65I have tried to use the & function to create a formula along the lines:-
Rich (BB code):
=Q2&T2&" "&U2&"]"&S2&"'!"&"$Q$65"Q2 is path using the formula =LEFT(CELL("filename"),FIND("[",CELL("filename"))-0). This leaves the [ bracket in
T2 is =TEXT(B2,"MM") to build the month number
U2 is =TEXT(B2,"mmmm") to build the full month name
S2 is =TEXT(B2+1,"dd-mmm-yy"), otherwise it inserts a numerical value after the month name. The b2+1 will return the correct name
The $Q$65 is the required cell value from the cell.
Having built up the formula in P2, I then refer to it in E2. All it does is look as though I have typed in text, doesn’t look like a formula. When I open Banking, it doesn’t offer to Update links.
I attach a copy of the workbook in question.
What is the way forward to automate this?
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2:A26 | A2 | =TEXT(B2,"ddd") |
| B2 | B2 | =Formula!D2 |
| E2 | E2 | =P2 |
| P2:P26 | P2 | =Q2&S2&" "&T2&U2&"]"&R2&"'!"&"$Q$65" |
| Q2:Q26 | Q2 | =LEFT(CELL("filename"),FIND("[",CELL("filename"))-0) |
| R2:R26 | R2 | =TEXT(B2+1,"dd-mmm-yy") |
| S2:S26 | S2 | =TEXT(B2,"MM") |
| T2:T26 | T2 | =TEXT(B2,"mmmm") |
| B3:B26 | B3 | =B2+7 |
| C12:D26,C11,C3:D10 | C3 | =M2 |
| E3:E6 | E3 | =P2 |
