2

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.

Improve this question
1
  • This is a bit tricky to follow. Please share a link to a sample workbook with the relevant formulas.
    – Blindspots ♦
    Sep 26, 2022 at 16:25

1 Answer 1

Reset to default
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)
)
Improve this answer
3
  • love this structure, I was not really looking for the actual R1C1 notation. I was assuming that when I put into a cell something like =A1 that when I copy it across, I get =B1; =C1; etc - and when I copy down I get =A2; =A3 and so on. This method is incredible and will try it, but is there a simpler method?
    – Sanford
    Sep 26, 2022 at 9:57
  • But it WORKS! Thanks David!
    – Sanford
    Sep 26, 2022 at 16:31
  • Glad to hear it works! I couldn't quite get a simpler solution, since dragging is important, but I just realized that maybe the ADDRESS function can help you. I'm not sure in what capacity, but it probably could.
    – David Tan
    Sep 26, 2022 at 23:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.