Will Conditional Formatting work?
 

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 cant be modified by adding rows or columns.

Will Conditional Formatting work?
 

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

"Lisa" wrote:

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 cant be modified by adding rows or columns.

Will Conditional Formatting work?
 

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


"bj" wrote:

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

"Lisa" wrote:

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 cant be modified by adding rows or columns.

Will Conditional Formatting work?
 

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 insertnamedefine

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


--
Regards,

Peo Sjoblom



"Lisa" wrote in message
...
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


"bj" wrote:

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

"Lisa" wrote:

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.

Will Conditional Formatting work?
 

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" wrote:

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 insertnamedefine

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


--
Regards,

Peo Sjoblom



"Lisa" wrote in message
...
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


"bj" wrote:

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

"Lisa" wrote:

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.

Will Conditional Formatting work?
 

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


--
Regards,

Peo Sjoblom



"Lisa" wrote in message
...
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" wrote:

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 insertnamedefine

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


--
Regards,

Peo Sjoblom



"Lisa" wrote in message
...
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


"bj" wrote:

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

"Lisa" wrote:

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.

Will Conditional Formatting work?
 

Thank you for all your help. It works great.

"Peo Sjoblom" wrote:

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


--
Regards,

Peo Sjoblom



"Lisa" wrote in message
...
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" wrote:

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 insertnamedefine

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


--
Regards,

Peo Sjoblom



"Lisa" wrote in message
...
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


"bj" wrote:

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

"Lisa" wrote:

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.