JOBID and MATERIAL are defined name ranges (eg. A2:B100)

B2:
=IF(ISERR(SMALL(IF(JOBID=A$2,ROW(INDIRECT("1:"&ROW S(JOBID)))),ROWS($1:1))),"",INDEX(MATERIAL,SMALL(I F(JOBID=A$2,ROW(INDIRECT("1:"&ROWS(JOBID)))),ROWS( $1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed

or this formula, it's slightly shorter:
B2:
=IF(COUNTIF(JOBID,A$2)=ROWS($1:1),INDEX(MATERIAL, SMALL(IF(JOBID=A$2,ROW(JOBID)-MIN(ROW(JOBID))+1),ROWS($1:1))),"")

ctrl+shift+enter, not just enter
copy down as far as needed


"rldjda" wrote:

SHEET1 SHEET2
A B A
B
1 JOBID MATERIAL 1 JOBID MATERIAL
2 8Job1 concrete 2
3 8Job2 drywall 3
4 8Job3 mason mix 4
5 8Job1 nails 5
6 8Job1 4x2 panels 6
7 7
8 8

#1) I need SHEET2 to gather & sort through data from sheet 1. I need to set
it up so that when I enter a JOBID in SHEET2 A2, "ALL" materials from SHEET1
associated "ONLY" with the specified JOBID shows up in the Material column of
SHEET2.

#2) Data from SHEET1 will continuously be changing on a daily basis as new
data (JOBID & MATERIAL) is added each day. I need to be able to set it up so
that as data is entered in SHEET1, data is also being automatically updated
in SHEET2.

For example, if today I enter 8Job1 in SHEET2 A2; the words "concrete,
nails, 4x2 panels" show in SHEET2 B2:4. Then, 5 additional materials with
the 8Job1 are entered throughout the rest of the day. Tomorrow morning I
would like to see those 5 new items in SHEET2.

NOTE: both numbers and text will be used in data cells (i.e. 8Job1).
NOTE: I am using EXCEL 2007.

Is this possible? Can anyone help?