I have two data source columns named Service (J1) and FCP (K1). Service
column is text data and FCP is number data like this:

Service FCP
AMMS 70
AMMS 13
Medical 20
Exec 6
Exec 19

*notice that there can be more than one FCP number to a service

I have a drop down box at (A1). The drop down box works fine, the user
clicks on it and is given a list of the Services that he can pick from.

Beside the drop down list in Column B I want to have appear all of the FCP
numbers (not a total of them but how they appear in the FCP source coulmn)
that relate to the Service that was selected from the drop down box like this:

[Column A] [Column B]
Dropdown FCP
*Service Selected* FCP1
FCP2
FCP3

I have been racking my brain for 3 days on this. Can anyone tell me how this
could be done?

One play which delivers it here ..

Put in M2: =IF(A$1="","",IF(K2=A$1,ROW(),""))
Copy M2 down to cover the max expected extent of data in col K. Leave M1
blank.

Then just place in B1:
=IF(ROW()COUNT(M:M),"",INDEX(L:L,SMALL(M:M,ROW()) ))
Copy B1 down to the same extent as done in col M. Col B will return the
required FCP results for the DV selection made in A1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"HAZBONES" wrote:
I have two data source columns named Service (J1) and FCP (K1). Service
column is text data and FCP is number data like this:

Service FCP
AMMS 70
AMMS 13
Medical 20
Exec 6
Exec 19

*notice that there can be more than one FCP number to a service

I have a drop down box at (A1). The drop down box works fine, the user
clicks on it and is given a list of the Services that he can pick from.

Beside the drop down list in Column B I want to have appear all of the FCP
numbers (not a total of them but how they appear in the FCP source coulmn)
that relate to the Service that was selected from the drop down box like this:

[Column A] [Column B]
Dropdown FCP
*Service Selected* FCP1
FCP2
FCP3

I have been racking my brain for 3 days on this. Can anyone tell me how this
could be done?

You said put the formula in column M2. Is that correct? I don't have anything
in the M column. I only have columns A,J, & K at the moment. Can you clarify
because I did as you wrote but nothing happens.

"Max" wrote:

One play which delivers it here ..

Put in M2: =IF(A$1="","",IF(K2=A$1,ROW(),""))
Copy M2 down to cover the max expected extent of data in col K. Leave M1
blank.

Then just place in B1:
=IF(ROW()COUNT(M:M),"",INDEX(L:L,SMALL(M:M,ROW()) ))
Copy B1 down to the same extent as done in col M. Col B will return the
required FCP results for the DV selection made in A1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"HAZBONES" wrote:
I have two data source columns named Service (J1) and FCP (K1). Service
column is text data and FCP is number data like this:

Service FCP
AMMS 70
AMMS 13
Medical 20
Exec 6
Exec 19

*notice that there can be more than one FCP number to a service

I have a drop down box at (A1). The drop down box works fine, the user
clicks on it and is given a list of the Services that he can pick from.

Beside the drop down list in Column B I want to have appear all of the FCP
numbers (not a total of them but how they appear in the FCP source coulmn)
that relate to the Service that was selected from the drop down box like this:

[Column A] [Column B]
Dropdown FCP
*Service Selected* FCP1
FCP2
FCP3

I have been racking my brain for 3 days on this. Can anyone tell me how this
could be done?

Errata, earlier was out of sync by 1 col as I wrongly took Service to be in
col K, FCP in col L, when it should be cols J and K

The set up should have read as:

Put in L2: =IF(A$1="","",IF(J2=A$1,ROW(),""))
Copy L2 down to cover the max expected extent of data in col J. Leave L1
blank.

Then just place in B1:
=IF(ROW()COUNT(L:L),"",INDEX(K:K,SMALL(L:L,ROW()) ))
Copy B1 down to the same extent as done in col L. Col B will return the
required FCP results for the DV selection made in A1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sorry, as stated in my followup - which crossed your response - the earlier
set up was out of sync by 1 col as I wrongly took Service to be in col K, FCP
in col L, when it should be cols J and K

The set up should have read as:

Put in L2: =IF(A$1="","",IF(J2=A$1,ROW(),""))
Copy L2 down to cover the max expected extent of data in col J. Leave L1
blank.

Then just place in B1:
=IF(ROW()COUNT(L:L),"",INDEX(K:K,SMALL(L:L,ROW()) ))
Copy B1 down to the same extent as done in col L. Col B will return the
required FCP results for the DV selection made in A1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"HAZBONES" wrote:
You said put the formula in column M2. Is that correct? I don't have anything
in the M column. I only have columns A,J, & K at the moment. Can you clarify
because I did as you wrote but nothing happens.

Thanks. That worked. Now I can go for a drink...

Haz

"Max" wrote:

Errata, earlier was out of sync by 1 col as I wrongly took Service to be in
col K, FCP in col L, when it should be cols J and K

The set up should have read as:

Put in L2: =IF(A$1="","",IF(J2=A$1,ROW(),""))
Copy L2 down to cover the max expected extent of data in col J. Leave L1
blank.

Then just place in B1:
=IF(ROW()COUNT(L:L),"",INDEX(K:K,SMALL(L:L,ROW()) ))
Copy B1 down to the same extent as done in col L. Col B will return the
required FCP results for the DV selection made in A1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

welcome
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"HAZBONES" wrote in message
...
Thanks. That worked. Now I can go for a drink...

Haz

If the service codes are grouped together like your sample:

Enter this formula in B1 and copy down to a number of cells that equals the
maximum count of any single service. For example, in your sample data AMMS
and Exec both appear the most times, 2. So you'd need to copy the formula to
at least 2 cells.

=IF(ROWS(B$1:B1)<=COUNTIF(J$2:J$6,A$1),INDEX(K$2:K $6,MATCH(A$1,J$2:J$6,0)+ROWS(B$1:B1)-1),"")

--
Biff
Microsoft Excel MVP


"HAZBONES" wrote in message
...
I have two data source columns named Service (J1) and FCP (K1). Service
column is text data and FCP is number data like this:

Service FCP
AMMS 70
AMMS 13
Medical 20
Exec 6
Exec 19

*notice that there can be more than one FCP number to a service

I have a drop down box at (A1). The drop down box works fine, the user
clicks on it and is given a list of the Services that he can pick from.

Beside the drop down list in Column B I want to have appear all of the FCP
numbers (not a total of them but how they appear in the FCP source coulmn)
that relate to the Service that was selected from the drop down box like
this:

[Column A] [Column B]
Dropdown FCP
*Service Selected* FCP1
FCP2
FCP3

I have been racking my brain for 3 days on this. Can anyone tell me how
this
could be done?