Grabbing Specific Fields from Worksheets from External Workbook - NO INDIRECT FORMULA

[wherdzik]

Is there a way I can grab specific fields from each sheet, each sheet is standardized, and avoiding the Indirect formula.

So from the 200 standardized sheets i would like to grab lets say cell c17. Is there a way to do that without using the Indirect formula? It seems as if this INDIRECT FUNCTION is pretty volatile.

Thank you.

[hrlngrv]

Yes as long as you accept a multiple step procedure.

If full pathnames were in column A from row 3 down, worksheet names in row 1 and cell addresses in row 2 from column C right.

B3: =REPLACE(A3,FIND("|",SUBSTITUTE(A3,"\","|",LEN(A3)-LEN(SUBSTITUTE(A3,"\",""))))+1,0,"[")&"]"

C3: ="='"&$B3&C$1&"'!"&C$2

Select B3:C3 and fill down as far as there are pathnames in column A, say down to row 202. Select C3:C202 and fill right as far as there are worksheet names and cell addresses in rows 1 and 2, say into column AZ.

Select C3, copy, and paste into C2 to keep a copy of the formula.

The C3:AZ202 formulas return TEXT which looks like simple external reference formulas. Select C3:AZ202, copy, and paste special as values on top of itself. This converts the FORMULAS returning text into text CONSTANTS which look like formulas. With that range still selected, press [Ctrl]+H to display the Replace dialog. Enter = as both Find what and Replace with, and click Replace All . Yes, this replaces = with =, but that effectively enters all the text constants AS FORMULAS. You'd then have a 200-row by 50-column range of external reference formulas.

That said, once you click Replace All, you might as well go have lunch because fetching that many values from likely closed workbooks will take A LOT OF TIME. You could reduce the time by using VBA as part of the process above. Basically copy C2, the cell with the original formula, paste into C3:AZ202, select C3:AZ202, copy and paste special as values. THEN step through each row in A3:A202, open the workbook with the pathname in the current row, select C:AZ in that row, replace = with = in all cells in that single-row range. Since the workbook would be open, the formulas should recalculate quickly. Then close that other workbook, and proceed to process the next row.

There's probably a clever Power Query way to do this, but this is the approach I use.