| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight duplicate entries matching 2 criteria in another workshe
In Excel 2000, Im told that I cant reference other worksheets or workbooks
when using conditional formatting. Looking through these forums, I put together a formula that highlights cells if there are duplicate listings of companies found in column S (row 2 has headers and the companies are listed in cells S3:S261. The conditional format in this Rates.xls file for S3, for example, is: Formula Is =COUNTIF(S:S,S3)1. I want to use a similar conditional format to highlight cells in column G of my other workbook (Test Rate) whereby I find pay rates from the Rates file based on TWO criteria: column E (company name) that matches values in column S of the Rates file; and column F, which concatenates columns B (Type), C (Program), & D (Model) with /s in the Test Rate file to match the values found in column K of the Rates file. Since Im told that I cant reference other worksheets or workbooks in the conditional format, Ive created a formula in column L which basically checks to see if BOTH conditions are met and returns either True or False. The formula in cell L8 is: =AND(COUNTIF('[Rates.xls]Sheet1'!$S:$S,$E8)1,COUNTIF('[Rates.xls]Sheet1'!$K:$K,$F8)1) My problem is that this formula is evaluating each condition independently and I need it to evaluate BOTH conditions TOGETHER. Currently this formula is incorrectly returning TRUE for an entry that should be FALSE because although there is 1 company listing in the Rates file (this evaluates as TRUE), AND there is 1 listing of the value in F8 (Networks/Res/Home; which also evaluates as TRUE), the combination of both items together results in only 1 value for each combination. In other words, even though there are 2 listings for Company A, the values in column F are different for each of the 2 listings. Column F of one of Company As listings is Networks/Res/School (of which there are 58 listings) and in the other of Company As listings, column F is Networks/Res/Home (of which there are 70 listings). Because the formula is currently evaluating each condition independently, the 2nd half of the formula is returning TRUE because there are multiple listings for this half, but evaluating BOTH conditions TOGETHER, the formula should return FALSE. How do I correct my formula? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Highlight duplicate entries matching 2 criteria in another workshe
use SUMPRODUCT()
-- Gary''s Student gsnu200709 "RS" wrote: In Excel 2000, Im told that I cant reference other worksheets or workbooks when using conditional formatting. Looking through these forums, I put together a formula that highlights cells if there are duplicate listings of companies found in column S (row 2 has headers and the companies are listed in cells S3:S261. The conditional format in this Rates.xls file for S3, for example, is: Formula Is =COUNTIF(S:S,S3)1. I want to use a similar conditional format to highlight cells in column G of my other workbook (Test Rate) whereby I find pay rates from the Rates file based on TWO criteria: column E (company name) that matches values in column S of the Rates file; and column F, which concatenates columns B (Type), C (Program), & D (Model) with /s in the Test Rate file to match the values found in column K of the Rates file. Since Im told that I cant reference other worksheets or workbooks in the conditional format, Ive created a formula in column L which basically checks to see if BOTH conditions are met and returns either True or False. The formula in cell L8 is: =AND(COUNTIF('[Rates.xls]Sheet1'!$S:$S,$E8)1,COUNTIF('[Rates.xls]Sheet1'!$K:$K,$F8)1) My problem is that this formula is evaluating each condition independently and I need it to evaluate BOTH conditions TOGETHER. Currently this formula is incorrectly returning TRUE for an entry that should be FALSE because although there is 1 company listing in the Rates file (this evaluates as TRUE), AND there is 1 listing of the value in F8 (Networks/Res/Home; which also evaluates as TRUE), the combination of both items together results in only 1 value for each combination. In other words, even though there are 2 listings for Company A, the values in column F are different for each of the 2 listings. Column F of one of Company As listings is Networks/Res/School (of which there are 58 listings) and in the other of Company As listings, column F is Networks/Res/Home (of which there are 70 listings). Because the formula is currently evaluating each condition independently, the 2nd half of the formula is returning TRUE because there are multiple listings for this half, but evaluating BOTH conditions TOGETHER, the formula should return FALSE. How do I correct my formula? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Highlight duplicate entries matching 2 criteria in another wor
Hi Gary's Student,
Since I've never used SUMPRODUCT before, I looked at the Excel Help (no help there) and the forums here to try and fix my equation. What I came up with is the following: =SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)1) However, I'm doing something wrong, because it's returning 0 for something that, with the other formula, gave a result of TRUE because there was 1 possible value for the 2 matching criteria. What am I doing wrong? "Gary''s Student" wrote: use SUMPRODUCT() -- Gary''s Student gsnu200709 "RS" wrote: In Excel 2000, Im told that I cant reference other worksheets or workbooks when using conditional formatting. Looking through these forums, I put together a formula that highlights cells if there are duplicate listings of companies found in column S (row 2 has headers and the companies are listed in cells S3:S261. The conditional format in this Rates.xls file for S3, for example, is: Formula Is =COUNTIF(S:S,S3)1. I want to use a similar conditional format to highlight cells in column G of my other workbook (Test Rate) whereby I find pay rates from the Rates file based on TWO criteria: column E (company name) that matches values in column S of the Rates file; and column F, which concatenates columns B (Type), C (Program), & D (Model) with /s in the Test Rate file to match the values found in column K of the Rates file. Since Im told that I cant reference other worksheets or workbooks in the conditional format, Ive created a formula in column L which basically checks to see if BOTH conditions are met and returns either True or False. The formula in cell L8 is: =AND(COUNTIF('[Rates.xls]Sheet1'!$S:$S,$E8)1,COUNTIF('[Rates.xls]Sheet1'!$K:$K,$F8)1) My problem is that this formula is evaluating each condition independently and I need it to evaluate BOTH conditions TOGETHER. Currently this formula is incorrectly returning TRUE for an entry that should be FALSE because although there is 1 company listing in the Rates file (this evaluates as TRUE), AND there is 1 listing of the value in F8 (Networks/Res/Home; which also evaluates as TRUE), the combination of both items together results in only 1 value for each combination. In other words, even though there are 2 listings for Company A, the values in column F are different for each of the 2 listings. Column F of one of Company As listings is Networks/Res/School (of which there are 58 listings) and in the other of Company As listings, column F is Networks/Res/Home (of which there are 70 listings). Because the formula is currently evaluating each condition independently, the 2nd half of the formula is returning TRUE because there are multiple listings for this half, but evaluating BOTH conditions TOGETHER, the formula should return FALSE. How do I correct my formula? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Highlight duplicate entries matching 2 criteria in another wor
You may need to enclose the 1 within the --
In any case, debug the SUMPRODUCT before using it for the formatting. Help is not very helpful, instead see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary's Student gsnu200709 "RS" wrote: Hi Gary's Student, Since I've never used SUMPRODUCT before, I looked at the Excel Help (no help there) and the forums here to try and fix my equation. What I came up with is the following: =SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)1) However, I'm doing something wrong, because it's returning 0 for something that, with the other formula, gave a result of TRUE because there was 1 possible value for the 2 matching criteria. What am I doing wrong? "Gary''s Student" wrote: use SUMPRODUCT() -- Gary''s Student gsnu200709 "RS" wrote: In Excel 2000, Im told that I cant reference other worksheets or workbooks when using conditional formatting. Looking through these forums, I put together a formula that highlights cells if there are duplicate listings of companies found in column S (row 2 has headers and the companies are listed in cells S3:S261. The conditional format in this Rates.xls file for S3, for example, is: Formula Is =COUNTIF(S:S,S3)1. I want to use a similar conditional format to highlight cells in column G of my other workbook (Test Rate) whereby I find pay rates from the Rates file based on TWO criteria: column E (company name) that matches values in column S of the Rates file; and column F, which concatenates columns B (Type), C (Program), & D (Model) with /s in the Test Rate file to match the values found in column K of the Rates file. Since Im told that I cant reference other worksheets or workbooks in the conditional format, Ive created a formula in column L which basically checks to see if BOTH conditions are met and returns either True or False. The formula in cell L8 is: =AND(COUNTIF('[Rates.xls]Sheet1'!$S:$S,$E8)1,COUNTIF('[Rates.xls]Sheet1'!$K:$K,$F8)1) My problem is that this formula is evaluating each condition independently and I need it to evaluate BOTH conditions TOGETHER. Currently this formula is incorrectly returning TRUE for an entry that should be FALSE because although there is 1 company listing in the Rates file (this evaluates as TRUE), AND there is 1 listing of the value in F8 (Networks/Res/Home; which also evaluates as TRUE), the combination of both items together results in only 1 value for each combination. In other words, even though there are 2 listings for Company A, the values in column F are different for each of the 2 listings. Column F of one of Company As listings is Networks/Res/School (of which there are 58 listings) and in the other of Company As listings, column F is Networks/Res/Home (of which there are 70 listings). Because the formula is currently evaluating each condition independently, the 2nd half of the formula is returning TRUE because there are multiple listings for this half, but evaluating BOTH conditions TOGETHER, the formula should return FALSE. How do I correct my formula? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Highlight duplicate entries matching 2 criteria in another wor
=SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)1) Perhaps try it as: =SUMPRODUCT(([Rates.xls]Sheet1!$S$3:$S$261=$E8)*([Rates.xls]Sheet1!$K$3:$K$261=$F8)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "RS" wrote: ...Since I've never used SUMPRODUCT before, I looked at the Excel Help (no help there) and the forums here to try and fix my equation. What I came up with is the following: =SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)1) However, I'm doing something wrong, because it's returning 0 for something that, with the other formula, gave a result of TRUE because there was 1 possible value for the 2 matching criteria. What am I doing wrong? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Highlight duplicate entries matching 2 criteria in another wor
Dear Garys Student,
Sorry for the delay in getting back to you, but this is the first I chance I had to see the new replies to my post. I tried your suggestion and came up with the following change to my equation: =SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E91),--('[Rates.xls]Sheet1'!$K$3:$K$261=$F91)) However, this simply counts all occurrences of all the companies in the Rates table. I tried Maxs solution below and that correctly displays the number of matching results found for the multiple criteria which are being evaluated. I took a look at the link that you provided, and while I havent finished reading the whole page yet, its helping me understand the SUMPRODUCT function. One thing I learned was that by using the SUMPRODUCT function, I dont need to keep my Rates workbook open to find the results. Thanks for your help and look forward to reading the rest of the link that you gave me. "Gary''s Student" wrote: You may need to enclose the 1 within the -- In any case, debug the SUMPRODUCT before using it for the formatting. Help is not very helpful, instead see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary's Student gsnu200709 "RS" wrote: Hi Gary's Student, Since I've never used SUMPRODUCT before, I looked at the Excel Help (no help there) and the forums here to try and fix my equation. What I came up with is the following: =SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)1) However, I'm doing something wrong, because it's returning 0 for something that, with the other formula, gave a result of TRUE because there was 1 possible value for the 2 matching criteria. What am I doing wrong? "Gary''s Student" wrote: use SUMPRODUCT() -- Gary''s Student gsnu200709 "RS" wrote: In Excel 2000, Im told that I cant reference other worksheets or workbooks when using conditional formatting. Looking through these forums, I put together a formula that highlights cells if there are duplicate listings of companies found in column S (row 2 has headers and the companies are listed in cells S3:S261. The conditional format in this Rates.xls file for S3, for example, is: Formula Is =COUNTIF(S:S,S3)1. I want to use a similar conditional format to highlight cells in column G of my other workbook (Test Rate) whereby I find pay rates from the Rates file based on TWO criteria: column E (company name) that matches values in column S of the Rates file; and column F, which concatenates columns B (Type), C (Program), & D (Model) with /s in the Test Rate file to match the values found in column K of the Rates file. Since Im told that I cant reference other worksheets or workbooks in the conditional format, Ive created a formula in column L which basically checks to see if BOTH conditions are met and returns either True or False. The formula in cell L8 is: =AND(COUNTIF('[Rates.xls]Sheet1'!$S:$S,$E8)1,COUNTIF('[Rates.xls]Sheet1'!$K:$K,$F8)1) My problem is that this formula is evaluating each condition independently and I need it to evaluate BOTH conditions TOGETHER. Currently this formula is incorrectly returning TRUE for an entry that should be FALSE because although there is 1 company listing in the Rates file (this evaluates as TRUE), AND there is 1 listing of the value in F8 (Networks/Res/Home; which also evaluates as TRUE), the combination of both items together results in only 1 value for each combination. In other words, even though there are 2 listings for Company A, the values in column F are different for each of the 2 listings. Column F of one of Company As listings is Networks/Res/School (of which there are 58 listings) and in the other of Company As listings, column F is Networks/Res/Home (of which there are 70 listings). Because the formula is currently evaluating each condition independently, the 2nd half of the formula is returning TRUE because there are multiple listings for this half, but evaluating BOTH conditions TOGETHER, the formula should return FALSE. How do I correct my formula? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Highlight duplicate entries matching 2 criteria in another wor
Dear Max,
Sorry for the delay in getting back to you, but this is the first I chance I had to see the new replies to my post. I tried what you suggested but Excel told me that the name wasnt defined. I noticed that there were no single quotes referring to the location of my lookup table in your equation, so I added them back in and then the formula correctly displayed the number of results matching the criteria given. The correct formula is: =SUMPRODUCT(('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)) I then simply added a conditional format that says if the text is 1, highlight the cell. What I like about SUMPRODUCT is that I can keep my Rates file closed and it will still evaluate the equation. Thank you for your help. I also took a look at the link of some of the solutions you have given to other people and found them to be very interesting as well. Using this same concept of calculating from a closed workbook, would it be possible to modify my current formula for displaying the rates from this Rates file while it is closed. Currently, my formula requires me to keep the Rates file open when looking-up the rates. Here is the array-entered formula: =INDEX('[Rates.xls]Sheet1'!O$3:O$261,MATCH(1,('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=CONCATENATE($B8,"/",$C8,"/",$D8)),0)) where from the Rates.xls file, column O contains the pay rates to be found and S contains the Company names to be matched against based on values in my test sheet. From my test sheet, row 1 has headers in it. Column B is Type, column C Program, column D Model, column E Company, and column G is where I want the rates to appear. In the formula, columns, B, C, & D are concatenated with /s to match the values found in column K of the Rates sheet (Example of column K: Networks/Res/Home; following the format $B8/$C8/$D8). The bottom half of this post was actually part of another question I had originally posted back on March 1. After 2 posters didnt provide a solution, I tried reposting the question 5 days later. No one replied to that post either. If you could take a look at that original post that would be greatly appreciated. Here is the link: http://www.microsoft.com/office/comm...a-0f3db8c89393 Thanks again for your help. "Max" wrote: =SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)1) Perhaps try it as: =SUMPRODUCT(([Rates.xls]Sheet1!$S$3:$S$261=$E8)*([Rates.xls]Sheet1!$K$3:$K$261=$F8)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "RS" wrote: ...Since I've never used SUMPRODUCT before, I looked at the Excel Help (no help there) and the forums here to try and fix my equation. What I came up with is the following: =SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)1) However, I'm doing something wrong, because it's returning 0 for something that, with the other formula, gave a result of TRUE because there was 1 possible value for the 2 matching criteria. What am I doing wrong? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Highlight duplicate entries matching 2 criteria in another wor
From my tests here, your array INDEX/MATCH seems to be able to update ok
from the closed Rates file. But if you want to try the equiv. SP version, here it is, right out of the formula cell (with rates.xls** open): =SUMPRODUCT(([rates.xls]Sheet1!$S$3:$S$261=$E8)*([rates.xls]Sheet1!$K$3:$K$261=$B8&"/"&$C8&"/"&$D8),[rates.xls]Sheet1!$O$3:$O$261) (simplified the CONCAT bit of it) **my test dependent file was named: rates.xls For the SP equivalent to work, it assumes col O in rates.xls contain numbers (not text), and that there's only one unique match within the criteria array. If there's more than 1 match within the array, then it'll return the sum of all the payrates in col A corresponding to the matches which of course is a wrong return here. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "RS" wrote in message ... Dear Max, Sorry for the delay in getting back to you, but this is the first I chance I had to see the new replies to my post. I tried what you suggested but Excel told me that the name wasn't defined. I noticed that there were no single quotes referring to the location of my lookup table in your equation, so I added them back in and then the formula correctly displayed the number of results matching the criteria given. The correct formula is: =SUMPRODUCT(('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)) I then simply added a conditional format that says if the text is 1, highlight the cell. What I like about SUMPRODUCT is that I can keep my Rates file closed and it will still evaluate the equation. Thank you for your help. I also took a look at the link of some of the solutions you have given to other people and found them to be very interesting as well. Using this same concept of calculating from a closed workbook, would it be possible to modify my current formula for displaying the rates from this Rates file while it is closed. Currently, my formula requires me to keep the Rates file open when looking-up the rates. Here is the array-entered formula: =INDEX('[Rates.xls]Sheet1'!O$3:O$261,MATCH(1,('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=CONCATENATE($B8,"/",$C8,"/",$D8)),0)) where from the Rates.xls file, column O contains the pay rates to be found and S contains the Company names to be matched against based on values in my test sheet. From my test sheet, row 1 has headers in it. Column B is "Type", column C "Program", column D "Model", column E "Company", and column G is where I want the rates to appear. In the formula, columns, B, C, & D are concatenated with /'s to match the values found in column K of the Rates sheet (Example of column K: Networks/Res/Home; following the format $B8/$C8/$D8). The bottom half of this post was actually part of another question I had originally posted back on March 1. After 2 posters didn't provide a solution, I tried reposting the question 5 days later. No one replied to that post either. If you could take a look at that original post that would be greatly appreciated. Here is the link: http://www.microsoft.com/office/comm...a-0f3db8c89393 Thanks again for your help. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Highlight duplicate entries matching 2 criteria in another wor
whoops, typo in line
sum of all the payrates in col A it should read: sum of all the payrates in col O -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Highlight duplicate entries matching 2 criteria in another wor
Dear Max,
Youre right about the INDEX/MATCH working with a closed workbook. I have been learning so many new functions trying to solve my multiple pay rate problem that I mixed up INDEX/MATCH with the COUNTIF fx (which doesnt work with a closed workbook), which is what someone originally suggested I use before you provided the correct SUMPRODUCT solution. For the most part, the current INDEX/MATCH formula works fine, but 14 of the companies have more than 1 pay rate available. For these companies, the formula only finds the first instance. Is there a way that I could have a dependent drop down list of results for these companies without sorting the source data located in the Rates file or using any filters. I cant have multiple results spread out over more than 1 cell (because this will result in many listings with blank cells, thus changing the format of my sheet) nor do I want to have the results concatenated into 1 long cell. I know that Debra Dalgleish has described how to create dependent drop-down lists on her site, but in those cases, it requires either having a sorted list or defining names for each list. Since I have almost 260 companies in the Rates file with other data listed in other columns, I would rather not sort this list or try and define names for all these companies. Maybe the solution to this would be to have some sort of combination of this INDEX/MATCH formula and vba or data validation that would provide a list for these 14 companies. Maybe something that would say, use the formula, but if any of these 14 companies with their multiple rates are found, use a data-validated list or vba to display the results in a list. Would this be possible? "Max" wrote: whoops, typo in line sum of all the payrates in col A it should read: sum of all the payrates in col O -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Highlight duplicate entries matching 2 criteria in another wor
I'm out here, sorry. Perhaps you might want to try a post
in .programming for a vba solution. Good luck ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- On Mar 14, 3:04 pm, RS wrote: Dear Max, You're right about the INDEX/MATCH working with a closed workbook. I have been learning so many new functions trying to solve my multiple pay rate problem that I mixed up INDEX/MATCH with the COUNTIF fx (which doesn't work with a closed workbook), which is what someone originally suggested I use before you provided the correct SUMPRODUCT solution. For the most part, the current INDEX/MATCH formula works fine, but 14 of the companies have more than 1 pay rate available. For these companies, the formula only finds the first instance. Is there a way that I could have a dependent drop down list of results for these companies without sorting the source data located in the Rates file or using any filters. I can't have multiple results spread out over more than 1 cell (because this will result in many listings with blank cells, thus changing the format of my sheet) nor do I want to have the results concatenated into 1 long cell. I know that Debra Dalgleish has described how to create dependent drop-down lists on her site, but in those cases, it requires either having a sorted list or defining names for each list. Since I have almost 260 companies in the Rates file with other data listed in other columns, I would rather not sort this list or try and define names for all these companies. Maybe the solution to this would be to have some sort of combination of this INDEX/MATCH formula and vba or data validation that would provide a list for these 14 companies. Maybe something that would say, use the formula, but if any of these 14 companies with their multiple rates are found, use a data-validated list or vba to display the results in a list. Would this be possible? |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Highlight duplicate entries matching 2 criteria in another wor
Dear Max,
Thanks anyway for your help in fixing this aspect of my problem. Will try your suggestion and post in the Programming forum. "Max" wrote: I'm out here, sorry. Perhaps you might want to try a post in .programming for a vba solution. Good luck ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- On Mar 14, 3:04 pm, RS wrote: Dear Max, You're right about the INDEX/MATCH working with a closed workbook. I have been learning so many new functions trying to solve my multiple pay rate problem that I mixed up INDEX/MATCH with the COUNTIF fx (which doesn't work with a closed workbook), which is what someone originally suggested I use before you provided the correct SUMPRODUCT solution. For the most part, the current INDEX/MATCH formula works fine, but 14 of the companies have more than 1 pay rate available. For these companies, the formula only finds the first instance. Is there a way that I could have a dependent drop down list of results for these companies without sorting the source data located in the Rates file or using any filters. I can't have multiple results spread out over more than 1 cell (because this will result in many listings with blank cells, thus changing the format of my sheet) nor do I want to have the results concatenated into 1 long cell. I know that Debra Dalgleish has described how to create dependent drop-down lists on her site, but in those cases, it requires either having a sorted list or defining names for each list. Since I have almost 260 companies in the Rates file with other data listed in other columns, I would rather not sort this list or try and define names for all these companies. Maybe the solution to this would be to have some sort of combination of this INDEX/MATCH formula and vba or data validation that would provide a list for these 14 companies. Maybe something that would say, use the formula, but if any of these 14 companies with their multiple rates are found, use a data-validated list or vba to display the results in a list. Would this be possible? |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Highlight duplicate entries matching 2 criteria in another wor
Welcome, RS.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "RS" wrote in message ... Dear Max, Thanks anyway for your help in fixing this aspect of my problem. Will try your suggestion and post in the Programming forum. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to highlight duplicate entries? | Excel Discussion (Misc queries) | |||
| Highlight repeat entries | Excel Discussion (Misc queries) | |||
| Highlight Duplicate on entry | Setting up and Configuration of Excel | |||
| highlight duplicate entries | Excel Discussion (Misc queries) | |||
| highlight duplicate numbers | Excel Discussion (Misc queries) | |||
Linear Mode
