Offset/Index/Match ... or something?

[Ken]

Excel2003 ...

WS1 ... contains list of all PN/Ops & Cols of other data ...

WS1 ... Range A2:A10000 ... contains record count (1 thru 9,999)
WS1 ... Range B2:B10000 ... contains Dept #'s
WS1 ... Range C2:C10000 ... contains PN's (may repeat up to 110 times)
WS1 ... Range D210000 ... contains Op #'s (sort = PN/Op# Ascend)

WS2 ... has identical Col layout & format, with intent to be for 1 PN only
(WS1 is list of all PNs) ... I could do this by Filtering PN on WS1 & then
Copy/Paste to WS2, However, I wish to:

WS2 ... Cell C2 ... enter a PN

WS2 ... Range C3:C200 ... Need Formula to return PN found in WS2 Cell C2 as
many times as found on WS1 Range C2:C10000 ... otherwise, return a Blank (so
if PN appeared on WS1 65 times ... the 1st 65 cells of WS2 Range C2:C200
would contain the PN with remaining cells in Range containing a Blank)

WS2 ... Range B2:B200 ... Need Formula to return Dept # found against PN
above from WS1 ... Range B2:B10000

WS2 ... Range D3200 ... Need Formula to return each Op # (Ascend) found
against PN above from WS1 ... Range D310000

Again ...

WS1 contains ALL PN/Ops (sort = Ascend)
WS2 to contain same data for 1 PN only based on PN entered in Cell C2

My "Thanks" in advance to those of you who are intimate with Excel & provide
the many valuable solutions found on these boards ... Kha

 

[macropod]

Hi Ken,

See: http://lounge.windowssecrets.com/index.php?showtopic=771787

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Ken]

Don ... (Hi)

I managed to figure this out & now have it working as desired ... Thanks for
supporting these boards ... Many fine solutions have been learned here ... Kha