Automatic formatting help?

M

Mel

My tracking workbook has 2 worksheets. The first is a list of delivery note
numbers. The 2nd is the tracking in its entirety. How can I make the
delivery note number on the first worksheet change colour or something
similar when it has been allocated on the main tracking worksheet?
 
T

Toppers

Mel,
Define the delivery notes on your tracking sheet as a named range
e.g. "DeliveryNotes".

Om Delivery note sheet, highlight column (A is example below) with delivery
notes and in Format=>Conditional Formatting:

Formula is: =match(A1,DeliveryNotes,0) and set Format to required colour

HTH
 
M

Max

Mel said:
My tracking workbook has 2 worksheets. The first is a list of delivery note
numbers. The 2nd is the tracking in its entirety. How can I make the
delivery note number on the first worksheet change colour or something
similar when it has been allocated on the main tracking worksheet?
Click to expand...

Another way to make CF work spanning across sheets
is to use INDIRECT ..

Assuming the allocations are done in Sheet2's col E (main tracking sheet)
and the list in Sheet1 is running down col A

In Sheet1,

Select col A
Click Format > Cond Formatting
Under Condition 1, make the settings:
Formula is: =MATCH(A1,INDIRECT("'Sheet2'!E:E"),0)
Click Format > Patterns tab > Green? > OK
Click OK at the main dialog
 
M

Mel

Thanks - sort of works but only some of the delivery notes that have been
used change? I've used all the delivery note numbers I've defined?
 
T

Toppers

Check there are no leading/trailing spaces on your numbers; otherwise it
should work.
 
M

Mel

Hi Max - thanks but not working, I'm obviously not getting it rightg. My
main tracking is in sheet 1, delivery notes are allocated in column 2 from
row 3 downwards. The list of delivery note numbers is in sheet 2 in column 1
from row 1 downwards?
 
M

Max

Mel said:
Hi Max - thanks but not working, I'm obviously not getting it rightg. My
main tracking is in sheet 1, delivery notes are allocated in column 2 from
row 3 downwards. The list of delivery note numbers is in sheet 2 in column 1
from row 1 downwards?
Click to expand...

In Sheet2,

Select col A, click Format > Cond Formatting,
then use the formula below in Cond 1, & Format to taste:
=MATCH(A1,INDIRECT("'Sheet1'!B:B"),0)

Here's a quick working sample to illustrate:
http://cjoint.com/?gusA4fpgts
CF_Across_Sheets.xls
(includes a CF dialog screenshot)
 
Top