Google Sheets conditional formatting based on relative reference using another sheet's cell values

2
votes

Using Google Spreadsheets, Excel doesn't seem to have the same problem.

I have a 100-cell column; In that column, I need every cell to check relative reference using another sheet's value. The problem is that conditional formatting seems to only accept INDIRECT, which unfortunately doesn't support relative reference.

I need Cell B3 to check Sheet2!$G3, and see if it is higher than 0, and if so, let the cell be green. Therefore I want Cell B4 to check Sheet2!G4; Ergo Cell B5 to check Sheet2!G5, and so on.

I haven't seen the issue to be addressed yet, will appreciate anyone's help.

2
google-sheetsgoogle-sheets-formulags-conditional-formatting

2 Answers

1
votes

try:

=B1>INDIRECT("Sheet2!G"&ROW())

0

0
votes

A basic formula-based CF rule works fine (in Excel)

enter image description here

EDIT - this worked for me in Sheets

=indirect("Sheet2!$G" & ROW()-2)>0

EDIT2 - on preview @player0 already has that