Path Variable in Formula

[JLouis]

I have a VLOOKUP formula in a worksheet as follows:
"=VLOOKUP("Total Disc / Coup",'M:\Dropbox\1 Administration\NEW MONTHLY EXTENSIONS 2022\MONTHLY GL\7\[276.dsr.xls]Sheet1'!$A$1:$P$22,9,FALSE)"
I've created a named cell reference on another sheet names "periodx" where the number 7 is located but the number changes based on the month. I'm looking for help writing the formula to use the correct numbered reference depending on the month such as:
"=VLOOKUP("Total Disc / Coup",'M:\Dropbox\1 Administration\NEW MONTHLY EXTENSIONS 2022\MONTHLY GL\"& periodx &"\[276.dsr.xls]Sheet1'!$A$1:$P$22,9,FALSE)"

Thank you in advance.

 

[Fluff]

Will the other workbook be open?

 

[JLouis]

Yes it is. It opens when the main worksheet opens and then closes during the opening sequence. I could leave it open if needed.

 

[Fluff]

Ok, you can use

=VLOOKUP("Total Disc / Coup",INDIRECT("'M:\Dropbox\1 Administration\NEW MONTHLY EXTENSIONS 2022\MONTHLY GL\"& periodx &"\[276.dsr.xls]Sheet1'!$A$1:$P$22"),9,FALSE)

although it will fail if the other workbook is closed.

 

[JLouis]

Thank you for the reply. When I copy and paste this formula with the other workbook opened I get a #REF error.

 

[Fluff]

Did you copy the formula using the copy icon in the top right of the tag?

 

[JLouis]

I did.
=VLOOKUP("Total Disc / Coup",INDIRECT("'M:\Dropbox\1 Administration\NEW MONTHLY EXTENSIONS 2022\MONTHLY GL\"& periodx &"\[276.dsr.xls]Sheet1'!$A$1:$P$22"),9,FALSE)

 

[Fluff]

Is the file stored on Dropbox?

 

[JLouis]

Yes, both files are in a Dropbox folder although they are stored locally. I moved the files over to my desktop but it didn't solve the issue.

 

[Fluff]

In that case check the the cell with 7 does not have anything else in it.