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