Automatic setup of the difference between two or several spreadsheets - is it possible?

[Razzy]

Hi!

*This time I will ask you before I spend hours on something that does not work in practice.

This is what the macro to produce:
I want to select lats say two spreadsheets, run a macro, and when the macro is done I want to be left with a separate spreadsheet, let's call it: "Compare", which shows = dynamic reference A - dynamic reference B.

Example for one cell:

Compare.xlsx
	A
1	-6
Ark1

Cell Formulas
Range	Formula
A1	A1	='C:\Users\Wayne\Downloads\[A.xlsx]Ark1'!$A$1-'C:\Users\Wayne\Downloads\[B.xlsx]Ark1'!$A$1

Comments:
- I have checked out the number of cells with formula, and we are in an area of 80,000. Surely this makes the macro will work forever?
- We are talking about large calculation templates for the company I work for.
- I have done the same with indirect formula, by having a preset template, but it is slow and works a lot.

My thoughts:
- I find filepath via filepicker.
- The macro must find all cells with formulas.
- Find the location and run a replacement.
- I think now that I have made this post, that the task is too big.

Do you have any other suggestions?

 

[Razzy]

I can set up a template with everything like:

='C:\Users\Wayne\Downloads\[A.xlsx]Ark1'!$A$1-'C:\Users\Wayne\Downloads\[B.xlsx]Ark1'!$A$1

as
=file1$A$1-file2$A$1

and then run search and replace on the whole spreadsheet?

 

[Razzy]

What is the limit of workbook references? And will it work faster than the indirect formula?

=file1$A$1-file2$A$1

=file1$A$2-file2$A$2

And so on.

*file1 and file2 I get from filepicker.

 

[Razzy]

Nwm then ill see how this goes.