Extract Row Number from Formula

[Adrian325]

Hi!

I have a workbook set up that pulls linked data from another workbook and summarizes it. The row numbers in these links will change from time to time, and I'd like to update the formulas using vba.

i.e. 1

=IF(OR(LEFT('Q:\[Equipment Master Listing.xlsx]Equip. MASTER Listing'!$A3,1)="1",'Q:\[Equipment Master Listing.xlsx]Equip. MASTER Listing'!$A3="Will"), 'Q:\[Equipment Master Listing.xlsx]Equip. MASTER Listing'!$A3,"")

i.e. 2

=IF(OR(LEFT('Q:\[Equipment Master Listing.xlsx]Equip. MASTER Listing'!$A560,1)="1",'Q:\[Equipment Master Listing.xlsx]Equip. MASTER Listing'!$A560="Will"), 'Q:\[Equipment Master Listing.xlsx]Equip. MASTER Listing'!$A560,"")

How can I extract the row number 3 or 560 from the formula, so I can edit and replace with the new row num?

[#2]

@Adrian325 

As often is the case, Excel offers more than one way to get from point A to point B.

I have a similar situation where, instead of using VBA or some other brute force method, I have a "utility tab" that stores changeable items like those cell references. I then use INDIRECT in the formula that does the calling of those remote sheets, and in the INDIRECT refer to the utility cells...

Here's a great reference that explains how to use INDIRECT

https://exceljet.net/excel-functions/excel-indirect-function