Using INDIRECT and a cell reference to compare values on sheets

Question

Simple story - driving me cray cray.

I have a single Google Workbook with sheets within with separate names (Sheet1, Sheet2, etc).

I have a separate sheet called Compare that I want to make formulas for each cell such that I can check if there is a difference between the cells on different sheets. For instance:

=IF(Sheet1!G4<>Sheet2!G4,Sheet1!G4&" | "&Sheet2!G4,"")

But I will use dropdowns on L2 and L3, so now I would assume I could do:

=IF(indirect(L2&"!G4")<>indirect(L3&"!G4"),indirect(L2&"!G4")&" | "&indirect(L3&"!G4"),"")

in my comparison sheet on cell G4.

Problem is that this is ONLY for matching for cell G4.

How do I make the reference malleable so when I copy down, the ROW number changes, and when I copy right, the COLUMN reference changes?

I have tried to convert the cell reference into a text, but have not found a way to do it.

Any recommendations? Thanks for any help.

Answer 1

Try this:

=IF(
    INDIRECT($L$2&"!R" & ROW() & "C" & COLUMN(),FALSE)
    <>
    INDRECT($L$3&"!R" & ROW() & "C" & COLUMN(), FALSE),

    INDIRECT($L$2&"!R" & ROW() & "C" & COLUMN(),FALSE) 
    & "|" & 
    INDRECT($L$3&"!R" & ROW() & "C" & COLUMN(), FALSE),
    
    "")
)

Using "!R" & ROW() & "C" & COLUMN() we use the R1C1 reference style. INDIRECT can use R1C1 by setting the second parameter to FALSE.

This does mean that you can only compare cells in two different cells with the same cell address. You can control this by making your own grid, with a column headers and row headers that you reference using lopsided absolute reference. $A1 for rows and A$1 for columns.

I suggest that you make a custom formula. It's a new feature that should have been rolled out for everyone by now. Go to Data > New Function > Add new function. Name it and give it a description. Argument names are leftvalue and rightvalue, though you can change that on your own disgression. the formula defintion goes:

IF(leftvalue <> rightvalue, leftvalue & "|" & rightvalue,"")

You can then add your descriptions and examples for each argument.

You can then write it more concisely as:

=COMPARE(
    INDIRECT($L$2 & "!R" & ROW() & "C" & COLUMN(),FALSE),
    INDRECT($L$3 &  "!R" & ROW() & "C" & COLUMN(),FALSE)
)