sorting data from one sheet to another

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?

Teethlessmama
Fri Mar 14 09:23:01 CDT 2008

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


"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?

GoBow777
Fri Mar 14 15:19:10 CDT 2008


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
Fri Mar 21 15:28:01 CDT 2008

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
>

GoBow777
Sat Mar 22 15:01:01 CDT 2008


> 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

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


--
GoBow777