Searching for multiple numbers

[darnduk]

I have spreadsheets from several Field Service Engineers with serial numbers
and other information. I want to select a column of serial numbers from
these spreadsheets, find and highlight those serial numbers in my workbook.
Doing one serial number at a time is tedious. Is there a way to get Excel to
do several at a time?

 

[porter444]

You could use conditional format for this...

Set a cell as the "key" and format cells if they equal the key value.

This should help you see how to make this work.
http://www.contextures.com/xlCondFormat02.html

--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott

 

[Shane Devenshire]

Hi,

Ok, if I understand you want to highlight the serial numbers in your
workbook which are also in the other files? If you want to keep those other
number in their own files here is what you would do:

1. In your spreadsheet, in an empty column, reference the first cell in one
of the serial number column in the other sheet, for example
='[10-21-2008.xls]Sheet2'!$A2

Copy this formula down as far as necessary. Say you put this in column
C1:C100

2. Select you serial numbers, suppose A1:A200

To conditionally format your cell(s):

In 2003:
1. Select the cell you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=OR(A1=$C$1:$C$100)
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=OR(A1=$C$1:$C$100)
5. Click the Format button and choose a format.
6. Click OK twice

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Shane Devenshire]

Hi,

I gave you one solution earlier, now here is another - which Microsoft says
can't be done.

1. In the Engineers workbooks name the range containing the serial numbers,
here I named it Data. (highlight the range and choose Insert, Name, Define,
type Data into the Names in workbook box and click OK).
2. Move to your workbook in an empty cell type = and then click on any cell
in the other workbook to build a formula like
'[10-21-2008.xls]Sheet2'!$A5
3. Edit this formula so it reads
'[10-21-2008.xls]Sheet2'!Data
Press Enter and the formula will change to
='10-21-2008.xls'!Data
4. Highlight the formula on the Formula Bar and copy it, press Enter once to
escape the Formula Bar
5. Choose the command Insert, Name, Define and enter a name like FSE1 in the
Names in workbook box (if you are using 2007 make it four letter followed by
a number to be on the safe side)
6. Click in the Refers to box, clear the existing entry and choose Paste.
This should paste the formula from step 3 into the Refers to box. Click OK.
7. Select the range with your serial numbers say A1:A100 and choose Format,
Conditional Formatting, Formula is from the first drop down, and in the
second box enter =OR(A1=FSE1) pick Format and choose what you want. Click
OK twice.

Remember Microsoft says you can't use external references in Conditional
Formatting, but apparently you can!

If this helps, please click the Yes button

Cheers,
Shane Devenshire