Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 -- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hiding zero values in charts | Charts and Charting in Excel | |||
Keeping a cell value constant trhoughout a list of values | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions |