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

Razzy

Board Regular
Joined
Jul 24, 2020
Messages
103
Office Version
  1. 365
Platform
  1. Windows
Hi! :giggle:

*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
RangeFormula
A1A1='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?
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Razzy

Board Regular
Joined
Jul 24, 2020
Messages
103
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 24, 2020
Messages
103
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 24, 2020
Messages
103
Office Version
  1. 365
Platform
  1. Windows
Nwm then ill see how this goes.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,086
Messages
5,545,878
Members
410,711
Latest member
Josh324
Top