using data from one sheet to populate multiple sheets 
Author Message
 using data from one sheet to populate multiple sheets

Is there a function that will populate data from one sheet
to another based on criteria. I am basically trying to use
the first sheet as a database to populate other sheets.
sheet1
date      dept  account    value
5/1/2002   1     xxxx       20.0
5/1/2002   1     abcd       30.0
5/1/2002   1     dfgh       40.0
6/1/2002   1     xxxx       20.0
6/1/2002   1     abcd       30.0
6/1/2002   1     dfgh       40.0
5/1/2002   2     xxxx       20.0
5/1/2002   2     abcd       30.0
5/1/2002   2     dfgh       40.0
6/1/2002   2     xxxx       20.0
6/1/2002   2     abcd       30.0
6/1/2002   2     dfgh       40.0
sheet2 criteria date 5/1/2002 and dept=1
date      dept  account    value
5/1/2002   1     xxxx       20.0
5/1/2002   1     abcd       30.0
5/1/2002   1     dfgh       40.0
sheet3 criteria date 6/1/2002 and dept=2
date      dept  account    value
6/1/2002   2     xxxx       20.0
6/1/2002   2     abcd       30.0
6/1/2002   2     dfgh       40.0


Tue, 28 Dec 2004 02:35:58 GMT  
 using data from one sheet to populate multiple sheets
Open help and search for 'lookup', if you don't get
anywhere, try the group again, but be more specific
about the criteria required.


Quote:
> Is there a function that will populate data from one sheet
> to another based on criteria. I am basically trying to use
> the first sheet as a database to populate other sheets.
> sheet1
> date      dept  account    value
> 5/1/2002   1     xxxx       20.0
> 5/1/2002   1     abcd       30.0
> 5/1/2002   1     dfgh       40.0
> 6/1/2002   1     xxxx       20.0
> 6/1/2002   1     abcd       30.0
> 6/1/2002   1     dfgh       40.0
> 5/1/2002   2     xxxx       20.0
> 5/1/2002   2     abcd       30.0
> 5/1/2002   2     dfgh       40.0
> 6/1/2002   2     xxxx       20.0
> 6/1/2002   2     abcd       30.0
> 6/1/2002   2     dfgh       40.0
> sheet2 criteria date 5/1/2002 and dept=1
> date      dept  account    value
> 5/1/2002   1     xxxx       20.0
> 5/1/2002   1     abcd       30.0
> 5/1/2002   1     dfgh       40.0
> sheet3 criteria date 6/1/2002 and dept=2
> date      dept  account    value
> 6/1/2002   2     xxxx       20.0
> 6/1/2002   2     abcd       30.0
> 6/1/2002   2     dfgh       40.0



Tue, 28 Dec 2004 02:42:42 GMT  
 using data from one sheet to populate multiple sheets
Sorry, just spotted your criteria.

=INDEX(Sheet1!$A$2:$D$12,SMALL(IF((Sheet1!$A$2:$A$12=$A2)*(Sheet1!$B$2:$B$12
=$B2),ROW(Sheet1!$A$2:$A$12)-ROW(Sheet1!A$2)+1),ROW()-1),3)

entered with Ctrl+Shift+Enter rather than simply Enter, into Sheet2!C2,
copy it into Sheet2!D2 and change the 3 to 4, then copy both down.

Untested, but should work, let us know if it doesn't give the
results you require.  Exactly the same formula applies to Sheet3, so
you could select both sheets before starting to type.

Steve D.


Quote:
> Open help and search for 'lookup', if you don't get
> anywhere, try the group again, but be more specific
> about the criteria required.



> > Is there a function that will populate data from one sheet
> > to another based on criteria. I am basically trying to use
> > the first sheet as a database to populate other sheets.
> > sheet1
> > date      dept  account    value
> > 5/1/2002   1     xxxx       20.0
> > 5/1/2002   1     abcd       30.0
> > 5/1/2002   1     dfgh       40.0
> > 6/1/2002   1     xxxx       20.0
> > 6/1/2002   1     abcd       30.0
> > 6/1/2002   1     dfgh       40.0
> > 5/1/2002   2     xxxx       20.0
> > 5/1/2002   2     abcd       30.0
> > 5/1/2002   2     dfgh       40.0
> > 6/1/2002   2     xxxx       20.0
> > 6/1/2002   2     abcd       30.0
> > 6/1/2002   2     dfgh       40.0
> > sheet2 criteria date 5/1/2002 and dept=1
> > date      dept  account    value
> > 5/1/2002   1     xxxx       20.0
> > 5/1/2002   1     abcd       30.0
> > 5/1/2002   1     dfgh       40.0
> > sheet3 criteria date 6/1/2002 and dept=2
> > date      dept  account    value
> > 6/1/2002   2     xxxx       20.0
> > 6/1/2002   2     abcd       30.0
> > 6/1/2002   2     dfgh       40.0



Tue, 28 Dec 2004 03:19:02 GMT  
 using data from one sheet to populate multiple sheets
Change the 3 and 4 to COLUMN() and give the formla
a name, e.g. fmla, then enter:

=IF(ISERROR(fmla),"",IF(fmla=0,"",fmla))

into every cell you wish to return data to. Strangely,
this does not need to be entered with Ctrl+Shift+Enter
once the formula has been given a name.

The thing to keep in mind is that the main formula is
now evaluated 3 times per cell and, once your data
list grows to a reasonable size, the calculation
time will increase substantially.

Please don't attach files in postings to the group,
it is unwelcomed by many due to lack of bandwith and
risk of viruses.  If you really can't explain things
clearly without a file (which you did, by the way)
let the group know that it is available by e-mail on
request.

TO THE REST OF THE GROUP:

Can anyone can explain why

=IF(ISERROR(fmla)+(fmla=0),"",fmla))

doesn't work in this case?

I know that OR() doesn't work in an array formula
but normally + would, wouldn't it?

I'm using xl2k if that makes a difference.

Steve D.


Quote:
> Hi Steve,
> Thanks for the formula.  I have tried the formula and it
> works great with two exceptions.  I have attached a sample
> spreadsheet to help display my two exceptions.  On sheet2
> when the criteria is met and sheet1 columns account and
> value are blank the formula returns 0 instead of blank. On
> Sheet2, when I copy the formula's down I get a #NUM when
> the criteria is no longer met.  How do I get rid of the
> error recognizing that the number of rows in sheet2 and
> sheet3 will vary.  Thanks for your help
> >-----Original Message-----
> >Sorry, just spotted your criteria.

> >=INDEX(Sheet1!$A$2:$D$12,SMALL(IF((Sheet1!$A$2:$A$12=$A2)*
> (Sheet1!$B$2:$B$12
> >=$B2),ROW(Sheet1!$A$2:$A$12)-ROW(Sheet1!A$2)+1),ROW()-
> 1),3)

> >entered with Ctrl+Shift+Enter rather than simply Enter,
> into Sheet2!C2,
> >copy it into Sheet2!D2 and change the 3 to 4, then copy
> both down.

> >Untested, but should work, let us know if it doesn't give
> the
> >results you require.  Exactly the same formula applies to
> Sheet3, so
> >you could select both sheets before starting to type.

> >Steve D.



> >> Open help and search for 'lookup', if you don't get
> >> anywhere, try the group again, but be more specific
> >> about the criteria required.



> >> > Is there a function that will populate data from one
> sheet
> >> > to another based on criteria. I am basically trying
> to use
> >> > the first sheet as a database to populate other
> sheets.
> >> > sheet1
> >> > date      dept  account    value
> >> > 5/1/2002   1     xxxx       20.0
> >> > 5/1/2002   1     abcd       30.0
> >> > 5/1/2002   1     dfgh       40.0
> >> > 6/1/2002   1     xxxx       20.0
> >> > 6/1/2002   1     abcd       30.0
> >> > 6/1/2002   1     dfgh       40.0
> >> > 5/1/2002   2     xxxx       20.0
> >> > 5/1/2002   2     abcd       30.0
> >> > 5/1/2002   2     dfgh       40.0
> >> > 6/1/2002   2     xxxx       20.0
> >> > 6/1/2002   2     abcd       30.0
> >> > 6/1/2002   2     dfgh       40.0
> >> > sheet2 criteria date 5/1/2002 and dept=1
> >> > date      dept  account    value
> >> > 5/1/2002   1     xxxx       20.0
> >> > 5/1/2002   1     abcd       30.0
> >> > 5/1/2002   1     dfgh       40.0
> >> > sheet3 criteria date 6/1/2002 and dept=2
> >> > date      dept  account    value
> >> > 6/1/2002   2     xxxx       20.0
> >> > 6/1/2002   2     abcd       30.0
> >> > 6/1/2002   2     dfgh       40.0

> >.



Tue, 28 Dec 2004 18:24:24 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. automaticly copying data from one sheet into another sheet using VBA

2. use date from one sheet to populate other sheets

3. Populating data from sheet 1 to sheet 2 based on column A

4. File Size - Major Difference of One Sheet Vs Multiple Sheets

5. Sorting list from one sheet onto multiple sheets?

6. collating multiple sheets on one master sheet

7. Sorting list from one sheet onto multiple sheets?

8. How to use data fron one sheet to print forms created in another sheet

9. Inserting data on one sheet from another sheet

10. HELP! Summarize sheet data on one sheet


 
Powered by phpBB® Forum Software © phpBB Group