Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
display list based on validation
Hello guys,
I have a BIG list of part number and serial numbers, in the thousands. In one sheet a have a dropdown box that has all the part numbers. what I would like to do is, have the sheet display all the serials of the part number I choose from the dropdown box. So if goe example I choose part number c123, in some point of the sheet all the serials corresponding to c123 will show up. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
display list based on validation
Suppose the part numbers are in column A of Sheet1, starting on row 2.
Then in a helper column (eg D2) you can put this formula: =IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2)) and copy it down as far as you like, even beyond your 4000 rows of data. This will give you a unique sequential number for each serial number, linked to each part number, like this: c123_1 c123_2 d123_1 c123_3 and so on. You will get a hyphen where column A is blank, so this helps to show where you have copied the formula to. Then on a separate sheet, suppose you use A1 to select the part number. In B1 you can have this formula: =IF($A$1="","",IF(ISNA(MATCH($A$1&"_"&ROW(A1),Shee t1!D:D, 0)),"",INDEX(Sheet1!B:B,MATCH($A$1&"_"&ROW(A1),She et1!D:D,0)))) Then you can copy this down as far as you need to - you will just get blanks if you copy it too far. Hope this helps. Pete On Oct 17, 6:03*pm, LUIS ANGEL wrote: Hello guys, I have a BIG *list of part number and serial numbers, in the thousands. In one sheet a have a dropdown box that has all the part numbers. what I would like to do is, have the sheet display all the serials of the part number I choose from the dropdown box. So if goe example *I choose part number c123, in some point of the sheet all the serials corresponding to c123 will show up. Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
display list based on validation
See Debra's site for dependent DV lists
http://www.contextures.on.ca/xlDataVal02.html Gord On Mon, 17 Oct 2011 10:03:09 -0700 (PDT), LUIS ANGEL wrote: Hello guys, I have a BIG list of part number and serial numbers, in the thousands. In one sheet a have a dropdown box that has all the part numbers. what I would like to do is, have the sheet display all the serials of the part number I choose from the dropdown box. So if goe example I choose part number c123, in some point of the sheet all the serials corresponding to c123 will show up. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
display list based on validation
Thanks Guys
On Oct 17, 2:31*pm, Pete_UK wrote: Suppose the part numbers are in column A of Sheet1, starting on row 2. Then in a helper column (eg D2) you can put this formula: =IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2)) and copy it down as far as you like, even beyond your 4000 rows of data. This will give you a unique sequential number for each serial number, linked to each part number, like this: c123_1 c123_2 d123_1 c123_3 and so on. You will get a hyphen where column A is blank, so this helps to show where you have copied the formula to. Then on a separate sheet, suppose you use A1 to select the part number. In B1 you can have this formula: =IF($A$1="","",IF(ISNA(MATCH($A$1&"_"&ROW(A1),Shee t1!D:D, 0)),"",INDEX(Sheet1!B:B,MATCH($A$1&"_"&ROW(A1),She et1!D:D,0)))) Then you can copy this down as far as you need to - you will just get blanks if you copy it too far. Hope this helps. Pete On Oct 17, 6:03*pm, LUIS ANGEL wrote: Hello guys, I have a BIG *list of part number and serial numbers, in the thousands. In one sheet a have a dropdown box that has all the part numbers. what I would like to do is, have the sheet display all the serials of the part number I choose from the dropdown box. So if goe example *I choose part number c123, in some point of the sheet all the serials corresponding to c123 will show up. Any ideas?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display part of list dependant on Validation list selection | Excel Worksheet Functions | |||
The drop down menu of my validation list needs to display more. | Excel Discussion (Misc queries) | |||
Reset Validation lists to display 1st value in list | Excel Programming | |||
Control Display of Drop Down List in Validation | Excel Worksheet Functions | |||
Can I display comments in a validation list | Excel Worksheet Functions |