Problem: How can I display multiple results, based on multiple
criteria, in one cell...kind of like a dependent drop-down list of results,
without sorting the source data located in a different 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.

Background: After combing through many postings in the Excel forums, I
adapted one of the formulas to allow me to find companies that match multiple
criteria on one of my worksheets and then insert the pay rate for these
companies in a separate column on that worksheet based on a pay rate table
located, currently, in a different workbook (a separate Excel file). While
trying to find a solution to this problem, I’ve created a smaller test
version of the spreadsheet. The formula I’m using is an array-entered
forumula:

=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).

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

For the most part, the current 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. Maybe the solution to this would to
have some sort of combination of this 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? All help is greatly apprecitated.

Sounds like you are trying to do too much in one step. And making you life
complicated as a result.
Split it into steps, using a hidden worksheet if necessary to retrieve the
data.
Then massage the results to suit the display requirements.

NickHK

"RS" <(E-Mail Removed)> wrote in message
news:BA26B123-9E92-40C6-AABE-(E-Mail Removed)...
> Problem: How can I display multiple results, based on multiple
> criteria, in one cell...kind of like a dependent drop-down list of
results,
> without sorting the source data located in a different 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.
>
> Background: After combing through many postings in the Excel forums,
I
> adapted one of the formulas to allow me to find companies that match
multiple
> criteria on one of my worksheets and then insert the pay rate for these
> companies in a separate column on that worksheet based on a pay rate table
> located, currently, in a different workbook (a separate Excel file).
While
> trying to find a solution to this problem, I've created a smaller test
> version of the spreadsheet. The formula I'm using is an array-entered
> forumula:
>
>
=INDEX('[Rates.xls]Sheet1'!O$3:O$261,MATCH(1,('[Rates.xls]Sheet1'!$S$3:$S$26
1=$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).
>
> Now 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.
>
> For the most part, the current 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. Maybe the solution to this would
to
> have some sort of combination of this 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? All help is greatly apprecitated.