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?

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),Sheet1!D,
0)),"",INDEX(Sheet1!B:B,MATCH($A$1&"_"&ROW(A1),Sheet1!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 <upslavazq...@gmail.com> 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?

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
<(E-Mail Removed)> 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?

Thanks Guys


On Oct 17, 2:31*pm, Pete_UK <pete.ashu...@yahoo.com> 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),Sheet1!D,
> 0)),"",INDEX(Sheet1!B:B,MATCH($A$1&"_"&ROW(A1),Sheet1!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 <upslavazq...@gmail.com> 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 -