Conditional Formatting in Sequential Order in Sheets

Question

I hope this all comes out clearly, I am in need of some help to save some major time.

This is for sports betting tracking systems.

I am trying to create a conditional format to where when I enter a value in a cell, it will either change the font to "Green" or "Red" based on the value of a different cell on a separate sheet. For example I am entering a value of +120 in cell E2 on "Sheet1!". In a corresponding cell G1 on "Sheet2!", it determines that I have a positive number. So, therefore, I want that "+120" E2 cell on "Sheet1" to be green. And, vise-versa, red if "Sheet!G1" is a negative number.

I am able to successfully do this by applying two conditional formats to my one specific cell "E2" on "Sheet1"

=INDIRECT("Sheet2!G1")>0

and

=INDIRECT("Sheet2!G1")<0

My BIG question is, how do I do this in sequential order down my spreadsheet? I have over 500 rows that I want to apply this to, but I can't seem to find an easy way to make each row on "Sheet1!" coincide with the correct correlating cell on "Sheet2!". I don't want to individually go to each row and paste these custom formulas.

For instance, I want "Sheet1!E3" to read off from "Sheet2!G2" and then

"Sheet1!E4" to read off from "Sheet2!G3" and so on...

Any help is appreciated, and I'm sorry if I'm not being clear with my explanation, I am fairly new with Google Sheets and still getting a handle on it.

Answer 1

=INDIRECT("Sheet2!G" & ROW())>0 and =INDIRECT("Sheet2!G" & ROW())<0

This should do it. Since the function takes in a string, we can just construct the string ourselves by concatenating (&) "Sheet2!G" and the row number which you can get using the ROW() function. Pretty clever use of indirect on your part. Pretty great for someone who says their new to Google Sheets.

Answer 2

You can apply the next custom formula in conditional formatting to the whole range E2:E

=Sheet2!G1 > 0 

The rest will drag automatically!