Automate Formulas to other workbooks.

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
132
Office Version
  1. 2013
Platform
  1. Windows
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:-

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$65

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:-

Rich (BB code):
='Z:\2019\[01 January.xlsx]06-Jan'!$Q$65

Whereas for January 2020, the formula would have read:-

Rich (BB code):
='Z:\2020\[01 January.xlsx]05-Jan'!$Q$65

Whereas for January 2021, the formula would have read:-

Rich (BB code):
='Z:\2021\[01 January.xlsx]03-Jan'!$Q$65

I 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?

Banking.xlsx
ABCDEFPQRSTU
1DayDateCash Carried forwardCheques Carried forwardZ Reading + Other CashCheques From Weekly ReadingsPath BuildPathSheet NameMonth NumberMonth NumberFile Ext
2Sat04-Jan-20£0.00£0.00Z:\2020\[01 January.xlsm]05-Jan-20'!$Q$65Z:\2020\[01 January.xlsm]05-Jan-20'!$Q$65Z:\2020\[05-Jan-2001January.xlsm
3Sat11-Jan-20#VALUE!£0.00Z:\2020\[01 January.xlsm]05-Jan-20'!$Q$65Z:\2020\[01 January.xlsm]12-Jan-20'!$Q$65Z:\2020\[12-Jan-2001January.xlsm
4Sat18-Jan-20#VALUE!£0.00Z:\2020\[01 January.xlsm]12-Jan-20'!$Q$65Z:\2020\[01 January.xlsm]19-Jan-20'!$Q$65Z:\2020\[19-Jan-2001January.xlsm
5Sat25-Jan-20#VALUE!£0.00Z:\2020\[01 January.xlsm]19-Jan-20'!$Q$65Z:\2020\[01 January.xlsm]26-Jan-20'!$Q$65Z:\2020\[26-Jan-2001January.xlsm
6Sat01-Feb-20#VALUE!£0.00Z:\2020\[01 January.xlsm]26-Jan-20'!$Q$65Z:\2020\[02 February.xlsm]02-Feb-20'!$Q$65Z:\2020\[02-Feb-2002February.xlsm
7Sat08-Feb-20#VALUE!£0.00Z:\2020\[02 February.xlsm]09-Feb-20'!$Q$65Z:\2020\[09-Feb-2002February.xlsm
8Sat15-Feb-20£0.00£0.00Z:\2020\[02 February.xlsm]16-Feb-20'!$Q$65Z:\2020\[16-Feb-2002February.xlsm
9Sat22-Feb-20£0.00£0.00Z:\2020\[02 February.xlsm]23-Feb-20'!$Q$65Z:\2020\[23-Feb-2002February.xlsm
10Sat29-Feb-20£0.00£0.00Z:\2020\[02 February.xlsm]01-Mar-20'!$Q$65Z:\2020\[01-Mar-2002February.xlsm
11Sat07-Mar-20£0.00£4,758.92Z:\2020\[03 March.xlsm]08-Mar-20'!$Q$65Z:\2020\[08-Mar-2003March.xlsm
12Sat14-Mar-20£0.00£4,758.92Z:\2020\[03 March.xlsm]15-Mar-20'!$Q$65Z:\2020\[15-Mar-2003March.xlsm
13Sat21-Mar-20£0.00£4,758.92Z:\2020\[03 March.xlsm]22-Mar-20'!$Q$65Z:\2020\[22-Mar-2003March.xlsm
14Sat28-Mar-20£0.00£4,758.92Z:\2020\[03 March.xlsm]29-Mar-20'!$Q$65Z:\2020\[29-Mar-2003March.xlsm
15Sat04-Apr-20£0.00£4,758.92Z:\2020\[04 April.xlsm]05-Apr-20'!$Q$65Z:\2020\[05-Apr-2004April.xlsm
16Sat11-Apr-20£0.00£4,758.92Z:\2020\[04 April.xlsm]12-Apr-20'!$Q$65Z:\2020\[12-Apr-2004April.xlsm
17Sat18-Apr-20£0.00£4,758.92Z:\2020\[04 April.xlsm]19-Apr-20'!$Q$65Z:\2020\[19-Apr-2004April.xlsm
18Sat25-Apr-20£0.00£4,758.92Z:\2020\[04 April.xlsm]26-Apr-20'!$Q$65Z:\2020\[26-Apr-2004April.xlsm
19Sat02-May-20£0.00£4,758.92Z:\2020\[05 May.xlsm]03-May-20'!$Q$65Z:\2020\[03-May-2005May.xlsm
20Sat09-May-20£0.00£4,758.92Z:\2020\[05 May.xlsm]10-May-20'!$Q$65Z:\2020\[10-May-2005May.xlsm
21Sat16-May-20£0.00£4,758.92Z:\2020\[05 May.xlsm]17-May-20'!$Q$65Z:\2020\[17-May-2005May.xlsm
22Sat23-May-20£0.00£4,758.92Z:\2020\[05 May.xlsm]24-May-20'!$Q$65Z:\2020\[24-May-2005May.xlsm
23Sat30-May-20£0.00£4,758.92Z:\2020\[05 May.xlsm]31-May-20'!$Q$65Z:\2020\[31-May-2005May.xlsm
24Sat06-Jun-20£0.00£4,758.92Z:\2020\[06 June.xlsm]07-Jun-20'!$Q$65Z:\2020\[07-Jun-2006June.xlsm
25Sat13-Jun-20£0.00£4,758.92Z:\2020\[06 June.xlsm]14-Jun-20'!$Q$65Z:\2020\[14-Jun-2006June.xlsm
26Sat20-Jun-20£0.00£4,758.92Z:\2020\[06 June.xlsm]21-Jun-20'!$Q$65Z:\2020\[21-Jun-2006June.xlsm
Banking
Cell Formulas
RangeFormula
A2:A26A2=TEXT(B2,"ddd")
B2B2=Formula!D2
E2E2=P2
P2:P26P2=Q2&S2&" "&T2&U2&"]"&R2&"'!"&"$Q$65"
Q2:Q26Q2=LEFT(CELL("filename"),FIND("[",CELL("filename"))-0)
R2:R26R2=TEXT(B2+1,"dd-mmm-yy")
S2:S26S2=TEXT(B2,"MM")
T2:T26T2=TEXT(B2,"mmmm")
B3:B26B3=B2+7
C12:D26,C11,C3:D10C3=M2
E3:E6E3=P2
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Xl365

New Member
Joined
Dec 29, 2020
Messages
10
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
It looks like there are 2 issues to correct.
Firstly, the cell references are missing an apostrophe ' at the start.

The values are linked to references like:
Excel Formula:
='Z:\2021\[01 January.xlsx]03-Jan'!$Q$65

However, your concatenation formula (using the & symbol), omits the leading character. So, cell P2 or Q2 and so on need to be prefixed with:
Excel Formula:
="'"

Secondly, to use the generated formulas, you would need to use something like the INDIRECT function to create the link.
So, something like:
Excel Formula:
=INDIRECT(P2)

I believe this should sort the issue with the formulas themselves. I'm not sure whether having so many indirect functions would cause any performance issues. I believe you'd need the linked workbook to be open to ensure the linked values are updated.
 

Xl365

New Member
Joined
Dec 29, 2020
Messages
10
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
To add that:
I'd be minded to change the file naming and sheet naming conventions. This could simplify the process - appreciate you're already well down that route. But could future proof if it's an ongoing requirement.
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
132
Office Version
  1. 2013
Platform
  1. Windows
XI365,

Thank you for your suggestions, I will look into doing what you have recommended next Year.

All the best for 2021.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,589
Messages
5,597,053
Members
414,118
Latest member
moversnpackers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top