I am investigating the WORKDAY function, to find the Next Working Day after a specific date, taking into account weekends and bank holidays.
For commonality between spreadsheets, it seems to me to be a pretty neat idea (© Hitch-Hikers' Guide to the Galaxy) to have the table of holiday days in the first column of a spreadsheet, which is referenced by any other spreadsheet which requires to use the WORKDAY function.
Consequently (and deeply tediously) I have specified all the English Bank Holidays from 2021 thru' 2027 in cells A7:A65 of my "Bank Holidays.xlsx" spreadsheet, named this cell range "Holidays", and deleted worksheets Sheet2 and Sheet3, for simplicity.
In my test worksheet, I have two formulae:
=WORKDAY(A3,1,'T:\Spreadsheets\Bank Holidays.xlsx'!Holidays)
=WORKDAY(A3,1,'T:\Spreadsheets\[Bank Holidays.xlsx]Sheet1'!$A$7:$A$65)
which both work fine on the date held in cell A3.
I note:
a) the FULL PATH always seems to be needed for the "external" spreadsheet - is this correct?
b) if I use the cells' ranges (second formula) rather than the RANGE NAME, the formula is considerably more complicated, and requires the inclusion of Sheet1 even though this is the only worksheet in the "Bank Holidays.xlsx" spreadsheet - is this correct?
Thanks!
Named ranges across spreadsheets?
-
- PlatinumLounger
- Posts: 5408
- Joined: 24 Jan 2010, 08:33
- Location: A cathedral city in England
Named ranges across spreadsheets?
Last edited by John Gray on 02 May 2023, 13:51, edited 2 times in total.
John Gray
"(or one of the team)" - how your appointment letter indicates you won't be seeing the Consultant...
"(or one of the team)" - how your appointment letter indicates you won't be seeing the Consultant...
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 5408
- Joined: 24 Jan 2010, 08:33
- Location: A cathedral city in England
Re: Named ranges across spreadsheets?
Thanks, Hans - I suspected this would be the case.
And a PS: should =FORMULATEXT(B3) work on Excel 2010?
And a PS: should =FORMULATEXT(B3) work on Excel 2010?
John Gray
"(or one of the team)" - how your appointment letter indicates you won't be seeing the Consultant...
"(or one of the team)" - how your appointment letter indicates you won't be seeing the Consultant...
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Named ranges across spreadsheets?
No, the FORMULATEXT function was introduced in Excel 2013.
You might use a custom VBA function instead; on the one hand that would work in all versions; on the other hand, it requires users to allow macros.
You might use a custom VBA function instead; on the one hand that would work in all versions; on the other hand, it requires users to allow macros.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 5408
- Joined: 24 Jan 2010, 08:33
- Location: A cathedral city in England
Re: Named ranges across spreadsheets?
Thanks, Hans - the FORMULATEXT function would have been mildly useful for documentation; otherwise I have to do a copy and paste of the formula to another cell, and stick an ' in front!
John Gray
"(or one of the team)" - how your appointment letter indicates you won't be seeing the Consultant...
"(or one of the team)" - how your appointment letter indicates you won't be seeing the Consultant...
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Named ranges across spreadsheets?
If you wish, copy the following code into a module in the Visual Basic Editor:
You can then use
=MyFormula(E2)
instead of
=FORMULATEXT(E2)
Code: Select all
Function MyFormula(cell As Range) As String
MyFormula = cell.FormulaLocal
End Function
=MyFormula(E2)
instead of
=FORMULATEXT(E2)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 5408
- Joined: 24 Jan 2010, 08:33
- Location: A cathedral city in England
Re: Named ranges across spreadsheets?
Thanks, Hans!
John Gray
"(or one of the team)" - how your appointment letter indicates you won't be seeing the Consultant...
"(or one of the team)" - how your appointment letter indicates you won't be seeing the Consultant...