sorting data from one sheet to another

[rldjda]

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?

 

[Teethless mama]

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

B2:
=IF(ISERR(SMALL(IF(JOBID=A$2,ROW(INDIRECT("1:"&ROWS(JOBID)))),ROWS($1:1))),"",INDEX(MATERIAL,SMALL(IF(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

 

[GoBow777]

#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|
+-------------------------------------------------------------------+

 

[rldjda]

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]

Thanks GoBow777. Your example is very helpful, but when I was testin
it, Sheet1 stopped reading items after row 30 in Sheet2. How can
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 wa
not to have the purple fill ins? Thanks

rldjda:

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

The purple fill-ins are a result of Conditional Formatting, to remov
them click Format/Conditional Formatting/Clear Rules/Clear Rules fro
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 o
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.

Code
-------------------
=IF(AB4="","",OFFSET(Sheet1!$A$1,AB4-1,0)
-------------------


Paste this formula in cell AB4 and copy down to match the range o
column AA.

Code
-------------------
=IF(OR(ISERR(SMALL(AC:AC,ROW(1:1))),Sheet1!A4=""),"",MID(SMALL(AC:AC,ROW(1:1)),FIND(".",SMALL(AC:AC,ROW(1:1))),6)*100000
-------------------


Paste this formula in cell AC4 and copy down to row 3000.

Code
-------------------
=IF(OR(Sheet1!A4="",COUNTIF(Sheet1!$A$4:$A4,Sheet1!$A4)>1),"",COUNTIF(Sheet1!$A$4:$A$3000,"<"&Sheet1!$A4)+1+ROW()/100000
-------------------


Paste this formula in cell AD4 and copy down to row 3000.

Code
-------------------
=IF(Sheet1!A4="","",MID(SMALL(AE:AE,ROW(1:1)),FIND(".",SMALL(AE:AE,ROW(1:1))),6)*100000
-------------------


Paste this formula in cell AE4 and copy down to row 3000. The referenc
to cell $A$1 is the JOBID drop down button, change the reference t
whatever cell you want to place the drop down button.

Code
-------------------
=IF(OR(Sheet1!A4="",Sheet1!A4<>$A$1),"",COUNTIF(Sheet1!$A$4:$A$3000,"<"&Sheet1!$A4)+1+ROW()/100000
-------------------


Select the range A3:X3000 and press the delete key to clear out all th
formulas and labels, then paste this formula in cell A4 and copy down t
row 3000. Column A is the cell location for the JOBID in question.

Code
-------------------
=IF(ISNUMBER(AD4),"A"&AD4,""
-------------------


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.

Code
-------------------
=IF(ISERR(OFFSET(Sheet1!A$1,$AD4-1,0)),"",IF(OFFSET(Sheet1!A$1,$AD4-1,0)=0,"",OFFSET(Sheet1!A$1,$AD4-1,0))
-------------------


Select the cell you chose for your JOBID drop down button, (a
previously discussed) then click Data/Data Validation, the Dat
Validation dialog box will open, where it says Allow: click the arro
button and select List, in the Source: box type in this formula an
click the OK button.

Code
-------------------
=OFFSET(AA4,0,0,COUNT(AB:AB),1
-------------------


HT

+-------------------------------------------------------------------
+-------------------------------------------------------------------