Crossreferencing values between 2 spreadsheets

W

wolfsburg2

Hello all, new member and i need some help.

I compile custoemr impact on excel spreadsheets for work. We have a
list of critical customers that egt special attention. What i am trying
to do is come up with a way to crossreference the val;ues from 2
different sheets without havign to maually data sort.

The values will not be exactly the same so i would like to set it up so
that if the first x amount of characters are the same it will match. The
sheets are both alphabetical in ascending form. My Critical list is a
constant. My customer list will vary depending on the outage. The only
pertinant data will be in column A on both sheets.

as an example:

my current sheet would be something like:

cell A3: *bobs tires (customer id xxx)*
my critical list iwould be
cell a1: *bobs tires*

So what i would like to do is set up something so that recognizes these
2 cells as being the same, and will reflect on the current sheet as
critical with something like Bolding the custoemr name, or changing the
font to red etc.

Thanks in advance, if you need any more info please let me know.

Mike P
[email protected]
 
B

Biff

Hi!

You don't necessarily need to check the first n characters.

However, since the formatting is based on conditions from another sheet you
have to take some additional steps.

Since "My customer list will vary depending on the outage", you should
create a dynamic named range that refers to your customer list.

See instructions here:

http://contextures.com/xlNames01.html#Dynamic

Once you have the named range defined, assume that name is Customers, then
you can set the conditional formatting.

Assume the data on the critical sheet is in the range A1:A10.

Select that range, A1:A10
Goto Format>Conditional Formatting
Formula is: =COUNTIF(customers,A1&"*")
Click the Format button
Select the style(s) you want
OK out

Biff
 
W

wolfsburg2

Bif,
Thanks for the help. It isn't quite working.

I named the range on my customer list. I then name the range on m
critical list. I tried conditionally formatiign both. and it doesn'
work. I think the problem might be that the values are not exactly th
same from one sheet to the other.

example customer list shows customer name (customer identifier)
critical list only has custoemr name. customer identifiers can var
depending on the type of account so they are not a constant an
can/will vary between the same custoemr on different outages dependin
on the service impacted.

Bobs tires could have a location in NY with an ID of g47 and anothe
loc in PA with and ID of M52.

So for a customer list on a NY outage cell A10 would reflect Bobs Tire
(G47).

I have no Access to the databases needed to locate all the custome
id's.

Is this the variable that's holding me back? Is there a formula tha
will let me conditionally format less than a complete matching entry
 
B

Biff

Hi!

It's getting kind of late (2:45 AM) where I'm at.

I see that you're posting from Excelforum which allows attachments. Can you
upload a small sample file of your data?

I won't be able to get to it until tomorrow, though. Maybe someone else will
jump in before then. Either way, we'll get you straightened out! What I
explained should work but sometimes the instructions or explanation "get
lost in translation"!

Biff
 
W

wolfsburg2

I have attached a couple of example lists. I am trying to identify
common customers through red type or something similar. you will notice
the only customer for this example that does exist on both sheets is not
an exact match(due to the Customer identifier and the actual name
entry(merrill lynch and co inc vs merrill lynch). This is a common
case, and still needs to be recognized as a match.

Also the critical list will be a constant, the customer list will not.
i will have outages with 1 customer up to 700+. i need a way to format
simply every time.

Thanks again for your time.


+-------------------------------------------------------------------+
|Filename: critical list.txt |
|Download: http://www.excelforum.com/attachment.php?postid=4150 |
+-------------------------------------------------------------------+
 
M

Max

One play to try ..

Sample construct available at:
http://cjoint.com/?mxl3D5r2BE
wolfsburg2_wks.xls

Assuming the reference list (critical list*) is within K1:K100
and the source list is in cols A to H, from row1 down,

Select cols A to H (with A1 active)
Click Format > Cond Format
Formula Is:
=SUMPRODUCT(--ISNUMBER(SEARCH($K$1:$K$100,$A1))*($K$1:$K$100<>""))=1
Format to taste > OK out

Adapt the range in col K to suit
(use the smallest range sufficient to cover the critical list)

*Just paste over the critical list into the same sheet
as the source list (use an empty col to the right, eg: col K above)
 
R

Roger Govier

Very nice solution, Max.

Maybe the test should be >0 rather than =1, just in case somebody
inadvertently puts a customer in the critical list twice.

All the very best for Christmas and the New Year.

Regards

Roger Govier
 
M

Max

Very nice solution, Max.
Maybe the test should be >0 rather than =1, just in case somebody
inadvertently puts a customer in the critical list twice.
All the very best for Christmas and the New Year.
Click to expand...

Thanks, Roger. Good point there about using ">0" instead, in the CF formula.
All the best to you, too! Cheers.
 
B

Biff

Hmmm.....

I thought the OP wanted the critical list formatted?

If so, the Countif should work.

Biff
 
M

Max

.. I thought the OP wanted the critical list formatted?
Click to expand...

From these lines in the OP's orig. post:
.. my current sheet would be something like:
cell A3: *bobs tires (customer id xxx)*
.. will reflect on the current sheet as critical
Click to expand...

I had interp'd / read it the other way round <g>
 
Top