How to reference data from another sheet in a cond

Question

I have multiple spreadsheets in a single google doc and I am trying to use information from one spread sheet in a conditional formula. For example, if the document contained two spreadsheets (sheet_A and sheet_B), I am trying to create a conditional formula such that if there is text in sheet_A cell A2, then sheet_B cell A2 fills a certain color.

I have tried Apply to range = B2 and creating a custom formula =sheet_A!A2<>"". However, I keep getting an error that this is an invalid formula. I can use =sheet_A!A2 to pull the value from the other sheet and the formula =A2<>"" works for conditional formatting to change the color within the same sheet, I just can't seem to get these two functions to act together.

Many thanks for any thoughts on what I am doing wrong here.

Answer 1

You could use istext or len > 0 for your conditional also:

=LEN(INDIRECT("'Sheet2'!A2"))>0

OR

=ISTEXT(INDIRECT("'Sheet2'!A2"))

Answer 2

For some reason conditional formatting requires the INDIRECT function.

Try this as your custom formula:

=INDIRECT("sheet_A!A2")<>""