Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dropdown List that populates another column
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dropdown List that populates another column
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dropdown List that populates another column
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dropdown List that populates another column
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dropdown List that populates another column
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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dropdown List that populates another column
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 --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dropdown List that populates another column
welcome
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "HAZBONES" wrote in message ... Thanks. That worked. Now I can go for a drink... Haz |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dropdown List that populates another column
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dropdown List - list item endings not visible if column too narrow | Excel Discussion (Misc queries) | |||
Select a category from a list that populates related cells | Excel Worksheet Functions | |||
Selection in 1st list box populates 2nd list box | Excel Worksheet Functions | |||
Make input in one column determine dropdown list in another. | Excel Discussion (Misc queries) | |||
How to make an input in one column determine dropdown list in anot | Excel Worksheet Functions |