Crossreferencing values between 2 spreadsheets
 

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



--
wolfsburg2
------------------------------------------------------------------------
wolfsburg2's Profile: http://www.excelforum.com/member.php...o&userid=29866
View this thread: http://www.excelforum.com/showthread...hreadid=495703

Crossreferencing values between 2 spreadsheets
 

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 he

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 FormatConditional Formatting
Formula is: =COUNTIF(customers,A1&"*")
Click the Format button
Select the style(s) you want
OK out

Biff

"wolfsburg2" wrote
in message ...

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



--
wolfsburg2
------------------------------------------------------------------------
wolfsburg2's Profile:
http://www.excelforum.com/member.php...o&userid=29866
View this thread: http://www.excelforum.com/showthread...hreadid=495703

Crossreferencing values between 2 spreadsheets
 

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

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

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

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

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

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

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


--
wolfsburg2
------------------------------------------------------------------------
wolfsburg2's Profile: http://www.excelforum.com/member.php...o&userid=29866
View this thread: http://www.excelforum.com/showthread...hreadid=495703

Crossreferencing values between 2 spreadsheets
 

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

"wolfsburg2" wrote
in message ...

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

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

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

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

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

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

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


--
wolfsburg2
------------------------------------------------------------------------
wolfsburg2's Profile:
http://www.excelforum.com/member.php...o&userid=29866
View this thread: http://www.excelforum.com/showthread...hreadid=495703

Crossreferencing values between 2 spreadsheets
 

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 |
+-------------------------------------------------------------------+

--
wolfsburg2
------------------------------------------------------------------------
wolfsburg2's Profile: http://www.excelforum.com/member.php...o&userid=29866
View this thread: http://www.excelforum.com/showthread...hreadid=495703

Crossreferencing values between 2 spreadsheets
 

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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"wolfsburg2" wrote
in message ...

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 |
+-------------------------------------------------------------------+

--
wolfsburg2
------------------------------------------------------------------------
wolfsburg2's Profile:
http://www.excelforum.com/member.php...o&userid=29866
View this thread: http://www.excelforum.com/showthread...hreadid=495703

Crossreferencing values between 2 spreadsheets
 

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


Max wrote:
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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"wolfsburg2" wrote
in message ...

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 |
+-------------------------------------------------------------------+

--
wolfsburg2
------------------------------------------------------------------------
wolfsburg2's Profile:

http://www.excelforum.com/member.php...o&userid=29866

View this thread: http://www.excelforum.com/showthread...hreadid=495703

Crossreferencing values between 2 spreadsheets
 

"Roger Govier" wrote
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.

Thanks, Roger. Good point there about using "0" instead, in the CF formula.
All the best to you, too! Cheers.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Crossreferencing values between 2 spreadsheets
 

Hmmm.....

I thought the OP wanted the critical list formatted?

If so, the Countif should work.

Biff

"Max" wrote in message
...
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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"wolfsburg2"
wrote
in message ...

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 |
+-------------------------------------------------------------------+

--
wolfsburg2
------------------------------------------------------------------------
wolfsburg2's Profile:
http://www.excelforum.com/member.php...o&userid=29866
View this thread:
http://www.excelforum.com/showthread...hreadid=495703

Crossreferencing values between 2 spreadsheets
 

"Biff" wrote
.. I thought the OP wanted the critical list formatted?

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

I had interp'd / read it the other way round <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--