# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  >  Conditional Formatting with a custom formula help (Google Docs)

## joynerCN

I have two sheets.

In Sheet1, in cells B5:P30, I have course ID numbers.

In Sheet2, in column A, I have course ID numbers. In column B, I have 'Available' and 'Not Available'. These designate whether the course is offered.

I want to conditionally format the course IDs in Sheet1 based on whether the course is offered according to Sheet2.

Or, to put that more formally in Excel terms, I want to conditionally format B5:P30 in Sheet1 based on whether the value in column B of Sheet2 associated with the course number in column A of Sheet2 is 'Available' or 'Not Available'.

I've tried this with a VLOOKUP, but haven't been able to get it to work -- I consistently get an error in the formula, but the formula works fine in the sheet away from the conditional formatting dialog. I've also tried with a third sheet listing Available and Not Available in their own columns and using a COUNTIF, but that similarly gave me unresolvable errors.

One example I've tried is:

=LOOKUP(B5,Sheet2!$A:$A,Sheet2!$B:$B)="Available"

But that just says the formula has an error in it. The formula works fine outside the conditional formatting dialog, however.

Any suggestions?

----------


## 6StringJazzer

Someone here may be able to help you but please note that Google Docs is not Excel, although it tries to emulate many Excel features, and this is an Excel forum.

----------


## joynerCN

> Someone here may be able to help you but please note that Google Docs is not Excel, although it tries to emulate many Excel features, and this is an Excel forum.



Isn't Google Docs explicitly named in the title of this sub-forum? ("For Other Platforms(Mac, Google Docs, Mobile OS etc)")

----------


## joynerCN

After investigating further, it's become clear that both methods will work, but conditional formatting has difficulty looking at a different sheet. The formula works perfectly if the data is in the same sheet, but once I move it to a different one conditional formatting has trouble with the reference.

Any suggestions on using a reference to another sheet in a conditional formatting custom formula?

----------


## 6StringJazzer

> Isn't Google Docs explicitly named in the title of this sub-forum? ("For Other Platforms(Mac, Google Docs, Mobile OS etc)")



Indeed it is, a thousand apologies. I navigated here from the list of unanswered threads and hadn't noticed.

----------

