Automatic formatting help?
 

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?

Automatic formatting help?
 

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

"Mel" wrote:

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?

Automatic formatting help?
 

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

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Automatic formatting help?
 

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?

"Toppers" wrote:

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

"Mel" wrote:

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?

Automatic formatting help?
 

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

"Mel" wrote:

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?

"Toppers" wrote:

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

"Mel" wrote:

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?

Automatic formatting help?
 

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?

"Max" wrote:

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

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Automatic formatting help?
 

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

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)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---