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?

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?

Hello rldjda:

If you need an explenation I'll give it my best shot.

Thanks GoBow777. Your example is very helpful, but when I was testing it,
Sheet1 stopped reading items after row 30 in Sheet2. How can I change it so
that Sheet2 will read up to 3000 rows in Sheet1. Secondly, how do I add 20
more rows in Sheet2. Lastly, is there a way not to have the purple fill ins?
Thanks

"GoBow777" wrote:


rldjda;639515 Wrote:
#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).

Hello rldjda:

If you need an explenation I'll give it my best shot.


+-------------------------------------------------------------------+
|Filename: XGames.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=106|
+-------------------------------------------------------------------+



--
GoBow777

rldjda:

Keep in mind this idea is not really meant for large groups of data because it could cause your computer to slow and or lockup.

The purple fill-ins are a result of Conditional Formatting, to remove them click Format/Conditional Formatting/Clear Rules/Clear Rules from Entire Sheet, you should do this on both sheets.

I have to assume that you meant to say, how do I add 20 more columns? If that’s the case then lets assume your range of data is A4:X3000.

If you know how many different or unique JOBID’s there are, then on Sheet2 paste this formula in cell AA4 and copy down as far as needed, but if your not sure then copy down to row 3000.
Paste this formula in cell AB4 and copy down to match the range of column AA.
Paste this formula in cell AC4 and copy down to row 3000.
Paste this formula in cell AD4 and copy down to row 3000.
Paste this formula in cell AE4 and copy down to row 3000. The reference to cell $A$1 is the JOBID drop down button, change the reference to whatever cell you want to place the drop down button.
Select the range A3:X3000 and press the delete key to clear out all the formulas and labels, then paste this formula in cell A4 and copy down to row 3000. Column A is the cell location for the JOBID in question.
At this point you should probably save your workbook.
Paste this formula in cell B4 and copy down and across to cell Y3000. Label row 3 however you see fit.
Select the cell you chose for your JOBID drop down button, (as previously discussed) then click Data/Data Validation, the Data Validation dialog box will open, where it says Allow: click the arrow button and select List, in the Source: box type in this formula and click the OK button.
HTH