Named ranges across spreadsheets?

User avatar
John Gray
PlatinumLounger
Posts: 5401
Joined: 24 Jan 2010, 08:33
Location: A cathedral city in England

Named ranges across spreadsheets?

Post by John Gray »

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!
Last edited by John Gray on 02 May 2023, 13:51, edited 2 times in total.
John Gray

Venison is quiet deer, and quite dear.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Named ranges across spreadsheets?

Post by HansV »

a) Yes.
b) Yes.
Best wishes,
Hans

User avatar
John Gray
PlatinumLounger
Posts: 5401
Joined: 24 Jan 2010, 08:33
Location: A cathedral city in England

Re: Named ranges across spreadsheets?

Post by John Gray »

Thanks, Hans - I suspected this would be the case.

And a PS: should =FORMULATEXT(B3) work on Excel 2010?
John Gray

Venison is quiet deer, and quite dear.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Named ranges across spreadsheets?

Post by HansV »

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.
Best wishes,
Hans

User avatar
John Gray
PlatinumLounger
Posts: 5401
Joined: 24 Jan 2010, 08:33
Location: A cathedral city in England

Re: Named ranges across spreadsheets?

Post by John Gray »

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

Venison is quiet deer, and quite dear.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Named ranges across spreadsheets?

Post by HansV »

If you wish, copy the following code into a module in the Visual Basic Editor:

Code: Select all

Function MyFormula(cell As Range) As String
    MyFormula = cell.FormulaLocal
End Function
You can then use

=MyFormula(E2)

instead of

=FORMULATEXT(E2)
Best wishes,
Hans

User avatar
John Gray
PlatinumLounger
Posts: 5401
Joined: 24 Jan 2010, 08:33
Location: A cathedral city in England

Re: Named ranges across spreadsheets?

Post by John Gray »

Thanks, Hans!
John Gray

Venison is quiet deer, and quite dear.