How could I automatize comparing some columns in 2 sheets (between original and filled up by someone else), cell by cell to check if the formula in the compared cells is unchanged? I want to check if the sheets after filled up with data and sent back contains intact formulas. Thanks!
What’s your concept of “automatize”?
You may temporarily apply a ConditionalFormat to find differences in formulas.
What’s unchanged formulas? Can there bne sheet references e.g.? This would complicate any attempt to compare formulas as text.
You may stsrt with the Calc function FORM ULA() which returns the formula from the referenced cell as text.
But: Why should somebody enter formulas that already are present in one sheet anew into a secoind sheet? This must be done by Copy/Paste.
Thanks for your time, but JohnSUN’s idea works for me.
Automatization: I don’t have to look through 3-4000 lines in a number of sheets one by one
as for “But” : If something can go wrong it will.
If we are talking about formulas that return an error, then perhaps the Bugerra extension will help you.
If you want to make sure that the old and new formulas exactly match, then create a new auxiliary sheet, enter a formula similar to =IFERROR(IF(FORMULA($Sheet1.A1)=FORMULA($Sheet2.A1);"";"X");"") in A1 and stretch it over the entire range. The cells marked with an “X” are where the formulas have changed.
2 Likes
It works for me, thanks!
What about the case where a formula was deleted or damaged in the second place, and by that is no longer recognized as a formula by Calc? (The dangerous “other user” may have entered the expected result wrongly, e.g.)
What about formulas containing relative sheet references?
Well, there may not occur any sheet references, but the questioner didn’t tell something to that effect.
Of course you wouldn’t compare formulas of that number one after the other by eye. However, the comparison based on the FORMULA() function used in lots of cells will also cause some handiwork.
Mostly this wouldn’t pass as a case of “document automation”.
Anyway I still dont understand:
Why do you need to compare the formulas instead of copying the correct ones, and pasting them into the target positions?
If your situation is too complex for this proceeding the literal comparison of folrmula strings also won’t work.
If the formulas were originally pasted: Why are you afraid of damage? You could protect the cells used for them. Steps:
- Disable sheet protection for the target sheet.
- Paste in the formulas (best from an already cell-protected source).
- Make sure the used cells have the wanted protection attributes set.
- Enable sheet protection to make the cell protection work.
A not too malevolent “other user” now won’t do any harm to the formulas.
1 Like
Agree with @Lupp. A strange task that generates an equally strange job. Something is wrong here… Who controls whom, and who harms whom? The OP’s actions are mysterious.
The only sheet that serves its intended purpose must be protected, and that’s it. Damage to formulas should be impossible. Using a spreadsheet in any other way diminishes the user’s confidence in it.
And further. Always follow the rule: data should be stored once in one place. Otherwise, the correct data will be questioned.
I think you misunderstand the job. The job is to analyse, not to create or alter anything.
There is a top contractor calling for tender some subcontractors by sending out blank documents. There are a few sheets in every documents and blank means sheets are created like forms and invited subcontractors fill them up with data.
I didn’t create the original documents, neither filled up any of them. I was asked - as a third party - to analyse the documents regarding some points provided by the top contractor. I’ve also got some hints that there were some formulas giving wrong results.in the original document and they want to know how the participants of the tender handled those mistakes and if they have kept the integrity and structure of original document intact.
Hope this helps to clarify the why 
@aaharsanyi, in such a case, the solution you noted is incorrect.
E.g.:
$Sheet1.A1: =1+2
$Sheet2.A1: 3
Since there is no formula on the second sheet, =FORMULA($Sheet2.A1) will return #N/A, and IFERROR() will return "".
Yes I know. You have every right to wrap each of the FORMULA() functions in IFERROR() and display detailed information about non-matching cells instead of the “X” sign. You can make the formula very complicated and achieve brilliant results. Do you want to spend a few hours of your life on this?
Copy cell range(s) you want to compare to one empty document at the same position(s), save that document. Copy the other document’s cell range(s) you want to compare to a second empty document at the same position(s), save that document. Open the first saved-to document, choose Edit → Track Changes → Compare Document… and pick the second saved-to document. Inspect the Manage Changes dialog.
Or just compare the entire original documents. YMMV.
1 Like
Thanks! This sounds legit and the second option was tried by me. The problem was, that Calc shows the result of the comparison, but I couldn’t find way to save it in a file, then I could work with it more efficiently then browsing the lines.
.
Saving the result comparison would have been helpful in my case a lot. I might need to request as a feature, if this was not the last job of this type for me.
=IFNA(IF(OR(ISFORMULA($Sheet1.A1);ISFORMULA($Sheet2.A1));FORMULA($Sheet1.A1)=FORMULA($Sheet2.A1);"");"ERROR")
Remarks: formulas match (TRUE); formulas do not match (FALSE); one of two cells does not contain a formula (ERROR); there are no formulas ("").
You are interested in cells with mismatched formulas (FALSE) and deleted/added only on one of the compared sheets (ERROR).
And you can reveal the meaning of the error and find out if the formula was deleted or added on the second sheet.
=IFNA(IF(OR(ISFORMULA($Sheet1.A8);ISFORMULA($Sheet2.A8));FORMULA($Sheet1.A8)=FORMULA($Sheet2.A8);"");IFS(ISFORMULA($Sheet1.A8);"DELETED";ISFORMULA($Sheet2.A8);"ADDED";NA();"ERROR"))
In principle, the end of IFS formula ;NA();"ERROR" is optional.
Hope this helps…
File attached. Wrong cells are formatted using a conditional formatting.
compare-sheets.ods (11.6 KB)
File updated
Thank you Sir! You definitely looking for perfection and your solution could have saved a lot of time for me.
My imperfect solution was examine the columns supposed to contain formulas with ISFORMULA. I even did it before @JohnSUN gave the idea and I already knew that all cells I was need with formulas are true from that aspect.
Fixed.
@aaharsanyi, I fixed something and added a named formula with relative addressing (see AnyCell: =CompareCells). This saves you from having to contemplate the terrible formula. And set up conditional formatting.
CompareCells=IF(OR(ISERROR($Sheet1.A1);ISERROR($Sheet2.A1));"ERROR";IFNA(IF(OR(ISFORMULA($Sheet1.A1);ISFORMULA($Sheet2.A1));FORMULA($Sheet1.A1)=FORMULA($Sheet2.A1);"");IFS(ISFORMULA($Sheet1.A1);"DELETED";ISFORMULA($Sheet2.A1);"ADDED")))
The 'compare-sheets.ods' file above have been updated.
UPDATED:
Let me explain it for you. Reference to A1 in ‘Calc A1’ syntax means RC in ‘Excel R1C1’ syntax, that is, in the current row of the current column, that is, in the current cell. Same for all cells. A1, A2 … are all RC. The ‘Calc A1’ formula syntax is confusing but worth understanding. Not to be confused with $A$1, or $A1, or A$1: these are absolute and mixed references.
For example, when applying conditional formatting, cell A1 is each cell in the range(s) A1:A5;B11;C9 depending on which cell is active (see sreenshot):
This may be difficult to understand.
1 Like
Thank you for your effort I’ll will chew it up after I finished the summary on comparisons coz I have to get down to writing the summary of results my analysis.