Range variable from String (Chicken Before the Egg)

[Jeffrey Mahoney]

I don't think this is possible, but I'm going to ask

I have a dozen or so named ranges referencing ranges in other workbooks. I have thousands of formulas in a table that are using those range like:
=IFERROR(VLOOKUP(Z8,CCFCLink03,AB$1,FALSE),"")

What I'd like to do is reference a cell at the top of each column that has CCFCLink01, CCFCLink03, CCFCLink03, etc. This is so I don't have to edit the formula in each column.

I know about Indirect. It doesn't work with ranges outside the current WB.

 

[dreid1011]

Is the named range in the current book pointing to another book, or is the named range in the other book? I seem to be using INDIRECT with a named range in another book just fine.

Book1
	A	B	C
1	15	15	'Book1 12-18-2023.xlsx'!ProdCt
Sheet2

Cell Formulas
Range	Formula
A1	A1	=SUM('F:\Documents\_MISC\Excel Stuff\Book1 12-18-2023.xlsx'!ProdCt)
B1	B1	=SUM(INDIRECT(C1))

 

[dreid1011]

Is the named range in the current book pointing to another book, or is the named range in the other book? I seem to be using INDIRECT with a named range in another book just fine.

Book1
	A	B	C
1	15	15	'Book1 12-18-2023.xlsx'!ProdCt
Sheet2

Cell Formulas
Range	Formula
A1	A1	=SUM('F:\Documents\_MISC\Excel Stuff\Book1 12-18-2023.xlsx'!ProdCt)
B1	B1	=SUM(INDIRECT(C1))

Nevermind, I see that I now have a #REF! error after the book refreshed.

 

[dreid1011]

Too late to edit, but my only other thought would be to update the formulas with a worksheet change event when you change the names at the top of the columns.