Automatically update cells in worksheets

  • Thread starter Thread starter admad
  • Start date Start date
A

admad

Excel newbie here hoping someone can point me in the right direction...

Have a workbook that contains 3 worksheets...

ws1: Is a work detail list I enter the date and unique ID no. and
Company Name.

ws2: Contains a list of the first 30 Companies I have to visit each
month, and contains all the Company details such as address, tel no, ID
no.,

ws3: Contains a full list of all Companies within the area I'm working
that month, including the initial 30 dealers.

Now what I'm trying to do is when I enter the Company Name in ws1 it
will automatically update Format conditions in ws2 and 3...

ws2 I want to change the txt color of the Cells containing same data as
ws1 (Company Name, Company Id) and change the txt style (normal to
strikethrough for instance)

ws3 I simply want to have the letter S or R entered into a field when
again the cells match with ws1 (company name, company Id)

If anyone can help in anyway would be great as I've searched everywhere
but cant seem to find exactly what I want...

Thanks
 
Assume col A = Unique ID no.,
col B = Company Name, etc
with data in row2 down
for all 3 sheets (identical structure)

In Sheet2:
------------
In an empty col to the right, say col X
Put in X2: =MATCH(A2,Sheet1!A:A,0)
Copy X2 down
(Hide col X later, if desired)

Select A2

Click Format > Conditional Formatting

Under Condition 1, put the setting as:
Formula is | =NOT(ISNA($X2))

Click Format > Font tab > Red / Bold / Strikethrough > OK
Click OK at the main dialog

With A2 selected, double-click on the format painter icon (brush)
[Cursor will turn into a "brush"]

Select & "paint" over all the rows with data
(just select the row headers)

Press Esc when done to revert the cursor to normal

Sheet2 will now give you the desired formatting
when you update the Unique IDs in Sheet1

In Sheet3:
------------
Put in say, W2: =IF(ISNA(MATCH(A2,Sheet1!A:A,0)),"","R")
Copy W2 down

Col W will return "R" for cases matched
when you update the Unique IDs in Sheet1
 
Max just have to say thanks for that walk through you gave me...

It works a treat and was exactly what I was needing !!!

Zym
 
Back
Top