Mass Change Formulas

[MarkW78]

Hello,

I have a workbook that is collecting information from many other workbooks. An example formula that I am using is:

='[01.xlsx]Cover Sheet'!$E$23<strike></strike>

The row where this formula is located would be collecting several cells from this same workbook.

The next row would collect the same data, but from a different workbook (02.xlsx) located in the same directory.

Is there a way to make this change across all of the columns without having to replace the "01" with "02" in each cell?

Thanks,
Mark

 

[DRSteele]

Welcome to the forum.

Function INDIRECT is ideal for this purpose. Type in file names, sheet names and cell references right-wards. I used files on my computer, together with worksheet names and a cell reference. You might need to change from my file type xlsm to xlsx (or xls if you're still using a dated version of Excel). Copy the formula across as far as necessary.

For this to work, you must have the referenced files in an open state, otherwise, the formula will report a #REF! error upon re-calculation; there is no way around this issue, as far as I know.

references: https://support.microsoft.com/en-us/kb/213933
excel - Wildcard reference to another workbook with INDIRECT Function - Stack Overflow

	A	B	C
1	file name:	MrExcel posts10	MrExcel posts11
2	sheet name:	Sheet3	Sheet4
3	cell:	a2	a3
4
5		Marketing	Store B

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B5 =INDIRECT("'["&B1&".xlsm]"&B2&"'!"&B3) C5 =INDIRECT("'["&C1&".xlsm]"&C2&"'!"&C3) <tbody> </tbody>

<tbody>
</tbody>

 

[MarkW78]

This is an approach that I was unaware of, but I don't think this will work for me. As it stands, this workbook will be pulling from five different directories - each with 01.xlsx, 02.xlsx, 03.xlsx, etc. Since Excel will not allow two workbooks to be opened with the same file name, this won't work.

Any other way to do this?


Thanks,
Mark

 

[DRSteele]

Try as I might, I can't find an obvious solution, or any kind for that matter. Sorry.

There is probably a VBA solution, but that kind of wizardry is far beyond my abilities for now.

 

[AliGW]

Just use find and replace on the main Excel ribbon.