Will Conditional Formatting work?

[Guest]

Can someone tell me if this is possible and if so how can I do it?
This is my issue I have two tabs. Tab1 has information exported from an
access database telling me if the date is a completed, forecasted, overdue or
has no date. I am doing a vlookup to pull these dates into tab 2. I need a
way to change the cell shading on tab 2 based on the status listed on Tab 1
i.e. Apple date 1 should be gray (status completed), date 2 should be yellow
(overdue), Orange date 1 white (not due yet) and date2 should also be white
(no date).

Tab 1
Name date1 status date2 status
Apple 1/1/07 Completed 2/2/07 Over due
Orange 12/1/07 Forecasted No date


Tab 2
Name date1 date2
Apple 1/1/07 2/2/07
Orange 12/1/07

Please note tab 2 can’t be modified by adding rows or columns.

 

[Guest]

name the status range cell in Tab 1
you can then use conditional formatting using the named range in tab 2

 

[Guest]

Please explain?

When I put a formula into conditional formatting referencing tab 1 I get
error message "You may not use references to other worksheets or workbooks
for conditional Formatting Criteria"

Thanks for the help

 

[Peo Sjoblom]

You have to name the cell/range, select A2:A10 click in the namebox above
column A and type MyName then you can refer to A2:A10 as MyName (you can
also name using insert>name>define

so instead of referring to Sheet2!A2:A10 you can use MyName and conditional
formatting will work

 

[Guest]

Thank you for the help on naming my range. I am having problems coming up
with the formula I would use in the Conditional formatting value?
IF(VLOOKUP(A1,Lisa,3,FALSE)="Completed",?,?) So Tab 2 B:1 back ground should
be gray what do I put in the “?†or is this completely wrong?

Thank you again in advance

 

[Peo Sjoblom]

You need to get a TRUE of FALSE when it comes to conditional formatting and
when a condition is TRUE it should format accordingly so instead of using IF
just use

=VLOOKUP(A1,Lisa,3,FALSE)="Completed"

where I assume Lisa is the name of the range in the other sheet and that
should work

 

[Guest]

Thank you for all your help. It works great.

You need to get a TRUE of FALSE when it comes to conditional formatting and
when a condition is TRUE it should format accordingly so instead of using IF
just use

=VLOOKUP(A1,Lisa,3,FALSE)="Completed"

where I assume Lisa is the name of the range in the other sheet and that
should work