Formula to post data in another spreadsheet

[taxmom]

Hi,

I have been unsuccessful in trying to figure out a formula that will look at
data in one spreadsheet and post all of the items found into another
worksheet.

example: worksheet 1 contains daily transactions in separate columns:
date vendor ck# amount
1/5/05 abc printing 504 100
1/5/05 sbc telephone 505 100
1/5/05 super coups 506 300
2/7/05 abc printing 507 100
2/8/05 sbc telephone 508 300
2/30/05 abc printing 509 200

I need the abc printing worksheet to be able to pull all of the payment
data; date, ck#, & amount made to them that is listed on the daily
transaction worksheet. Even if it is not in order.

I want to eliminate doing a sort or pivot table everytime we want to look at
how much we have paid someone. I would like it to flow directly on to
another worksheet.

Can this be done?
I would appreciate any help I can get. Thanks

[filo666]

EASY EASY EASY.... You just need to use the =vlookup() function with an if
function.
I mean:

in a cell: If([Vendor column:column number]="abc printing",vlookup([Range
starting from the first cell of vendor column until last cell of
Amount],{1,2,3}),"")

ex.

cell b2 you will put: =if(sheet1!b2="abc
printing",vlookup(sheet1!$b$2:$d$7,1,)"")
cell c2 you will put: =if(sheet1!b2="abc
printing",vlookup(sheet1!$b$2:$d$7,2,)"")
cell d2 you will put: =if(sheet1!b2="abc
printing",vlookup(sheet1!$b$2:$d$7,3,)"")

and you copy the cells b2 c2 and d2 7 times to the bottom of the page¡¡¡

"taxmom" wrote:

> Hi,
>
> I have been unsuccessful in trying to figure out a formula that will look at
> data in one spreadsheet and post all of the items found into another
> worksheet.
>
> example: worksheet 1 contains daily transactions in separate columns:
> date vendor ck# amount
> 1/5/05 abc printing 504 100
> 1/5/05 sbc telephone 505 100
> 1/5/05 super coups 506 300
> 2/7/05 abc printing 507 100
> 2/8/05 sbc telephone 508 300
> 2/30/05 abc printing 509 200
>
> I need the abc printing worksheet to be able to pull all of the payment
> data; date, ck#, & amount made to them that is listed on the daily
> transaction worksheet. Even if it is not in order.
>
> I want to eliminate doing a sort or pivot table everytime we want to look at
> how much we have paid someone. I would like it to flow directly on to
> another worksheet.
>
> Can this be done?
> I would appreciate any help I can get. Thanks

[taxmom]

This looks good however,
I did a test and I get an error when the error comes up I select OK and the
system goes to the "" at the end. Is there something missing with the quotes?

to confirm the formula:
=IF(Sheet1!B2="ABC Printing",vlookup(Sheet1!B2:D7,1,)"")

thanks

"filo666" wrote:

> EASY EASY EASY.... You just need to use the =vlookup() function with an if
> function.
> I mean:
>
> in a cell: If([Vendor column:column number]="abc printing",vlookup([Range
> starting from the first cell of vendor column until last cell of
> Amount],{1,2,3}),"")
>
> ex.
>
> cell b2 you will put: =if(sheet1!b2="abc
> printing",vlookup(sheet1!$b$2:$d$7,1,)"")
> cell c2 you will put: =if(sheet1!b2="abc
> printing",vlookup(sheet1!$b$2:$d$7,2,)"")
> cell d2 you will put: =if(sheet1!b2="abc
> printing",vlookup(sheet1!$b$2:$d$7,3,)"")
>
> and you copy the cells b2 c2 and d2 7 times to the bottom of the page¡¡¡
>
> "taxmom" wrote:
>
> > Hi,
> >
> > I have been unsuccessful in trying to figure out a formula that will look at
> > data in one spreadsheet and post all of the items found into another
> > worksheet.
> >
> > example: worksheet 1 contains daily transactions in separate columns:
> > date vendor ck# amount
> > 1/5/05 abc printing 504 100
> > 1/5/05 sbc telephone 505 100
> > 1/5/05 super coups 506 300
> > 2/7/05 abc printing 507 100
> > 2/8/05 sbc telephone 508 300
> > 2/30/05 abc printing 509 200
> >
> > I need the abc printing worksheet to be able to pull all of the payment
> > data; date, ck#, & amount made to them that is listed on the daily
> > transaction worksheet. Even if it is not in order.
> >
> > I want to eliminate doing a sort or pivot table everytime we want to look at
> > how much we have paid someone. I would like it to flow directly on to
> > another worksheet.
> >
> > Can this be done?
> > I would appreciate any help I can get. Thanks

[Peo Sjoblom]

If you look in help for vlookup you'll see that you need a lookup value

=vlookup(lookup_value,Table,column_index,FALSE)

--

Regards,

Peo Sjoblom


"taxmom" <[email protected]> wrote in message
news:[email protected]...
> This looks good however,
> I did a test and I get an error when the error comes up I select OK and
the
> system goes to the "" at the end. Is there something missing with the
quotes?
>
> to confirm the formula:
> =IF(Sheet1!B2="ABC Printing",vlookup(Sheet1!B2:D7,1,)"")
>
> thanks
>
> "filo666" wrote:
>
> > EASY EASY EASY.... You just need to use the =vlookup() function with an
if
> > function.
> > I mean:
> >
> > in a cell: If([Vendor column:column number]="abc
printing",vlookup([Range
> > starting from the first cell of vendor column until last cell of
> > Amount],{1,2,3}),"")
> >
> > ex.
> >
> > cell b2 you will put: =if(sheet1!b2="abc
> > printing",vlookup(sheet1!$b$2:$d$7,1,)"")
> > cell c2 you will put: =if(sheet1!b2="abc
> > printing",vlookup(sheet1!$b$2:$d$7,2,)"")
> > cell d2 you will put: =if(sheet1!b2="abc
> > printing",vlookup(sheet1!$b$2:$d$7,3,)"")
> >
> > and you copy the cells b2 c2 and d2 7 times to the bottom of the page¡¡¡
> >
> > "taxmom" wrote:
> >
> > > Hi,
> > >
> > > I have been unsuccessful in trying to figure out a formula that will
look at
> > > data in one spreadsheet and post all of the items found into another
> > > worksheet.
> > >
> > > example: worksheet 1 contains daily transactions in separate columns:
> > > date vendor ck# amount
> > > 1/5/05 abc printing 504 100
> > > 1/5/05 sbc telephone 505 100
> > > 1/5/05 super coups 506 300
> > > 2/7/05 abc printing 507 100
> > > 2/8/05 sbc telephone 508 300
> > > 2/30/05 abc printing 509 200
> > >
> > > I need the abc printing worksheet to be able to pull all of the
payment
> > > data; date, ck#, & amount made to them that is listed on the daily
> > > transaction worksheet. Even if it is not in order.
> > >
> > > I want to eliminate doing a sort or pivot table everytime we want to
look at
> > > how much we have paid someone. I would like it to flow directly on to
> > > another worksheet.
> > >
> > > Can this be done?
> > > I would appreciate any help I can get. Thanks

[taxmom]

I can't believe I'm having trouble with the Vlookup. I went back to the
basic and I did pull up the help and I'm trying to do the example and can't
seem to get the example to work.

Just not my day! I will keep trying. I'm sure I will get it I just need to
figure out what I'm doing wrong.

thank you both for all of your help.



"Peo Sjoblom" wrote:

> If you look in help for vlookup you'll see that you need a lookup value
>
> =vlookup(lookup_value,Table,column_index,FALSE)
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "taxmom" <[email protected]> wrote in message
> news:[email protected]...
> > This looks good however,
> > I did a test and I get an error when the error comes up I select OK and
> the
> > system goes to the "" at the end. Is there something missing with the
> quotes?
> >
> > to confirm the formula:
> > =IF(Sheet1!B2="ABC Printing",vlookup(Sheet1!B2:D7,1,)"")
> >
> > thanks
> >
> > "filo666" wrote:
> >
> > > EASY EASY EASY.... You just need to use the =vlookup() function with an
> if
> > > function.
> > > I mean:
> > >
> > > in a cell: If([Vendor column:column number]="abc
> printing",vlookup([Range
> > > starting from the first cell of vendor column until last cell of
> > > Amount],{1,2,3}),"")
> > >
> > > ex.
> > >
> > > cell b2 you will put: =if(sheet1!b2="abc
> > > printing",vlookup(sheet1!$b$2:$d$7,1,)"")
> > > cell c2 you will put: =if(sheet1!b2="abc
> > > printing",vlookup(sheet1!$b$2:$d$7,2,)"")
> > > cell d2 you will put: =if(sheet1!b2="abc
> > > printing",vlookup(sheet1!$b$2:$d$7,3,)"")
> > >
> > > and you copy the cells b2 c2 and d2 7 times to the bottom of the page¡¡¡
> > >
> > > "taxmom" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have been unsuccessful in trying to figure out a formula that will
> look at
> > > > data in one spreadsheet and post all of the items found into another
> > > > worksheet.
> > > >
> > > > example: worksheet 1 contains daily transactions in separate columns:
> > > > date vendor ck# amount
> > > > 1/5/05 abc printing 504 100
> > > > 1/5/05 sbc telephone 505 100
> > > > 1/5/05 super coups 506 300
> > > > 2/7/05 abc printing 507 100
> > > > 2/8/05 sbc telephone 508 300
> > > > 2/30/05 abc printing 509 200
> > > >
> > > > I need the abc printing worksheet to be able to pull all of the
> payment
> > > > data; date, ck#, & amount made to them that is listed on the daily
> > > > transaction worksheet. Even if it is not in order.
> > > >
> > > > I want to eliminate doing a sort or pivot table everytime we want to
> look at
> > > > how much we have paid someone. I would like it to flow directly on to
> > > > another worksheet.
> > > >
> > > > Can this be done?
> > > > I would appreciate any help I can get. Thanks
>
>
>

[Joe Wroblewski]

Taxmom,

One way to try it is to simply set up formulas in the ABC Printing
worksheet to copy the main page data if the vendor = "ABC Printing".
You'll have blank spaces but won't have to sort.
A1 formula: =IF(Main!$C1<>"ABC Printing",Main!A1,"")
A2 formula: =IF(Main!$C2<>"ABC Printing",Main!A2,"")
....
B3 formula: =IF(Main!$C3<>"ABC Printing",Main!B3,"")
....
Good luck.
Joe W
[email protected]

taxmom wrote:

>Hi,
>
>I have been unsuccessful in trying to figure out a formula that will look at
>data in one spreadsheet and post all of the items found into another
>worksheet.
>
>example: worksheet 1 contains daily transactions in separate columns:
>date vendor ck# amount
>1/5/05 abc printing 504 100
>1/5/05 sbc telephone 505 100
>1/5/05 super coups 506 300
>2/7/05 abc printing 507 100
>2/8/05 sbc telephone 508 300
>2/30/05 abc printing 509 200
>
>I need the abc printing worksheet to be able to pull all of the payment
>data; date, ck#, & amount made to them that is listed on the daily
>transaction worksheet. Even if it is not in order.
>
>I want to eliminate doing a sort or pivot table everytime we want to look at
>how much we have paid someone. I would like it to flow directly on to
>another worksheet.
>
>Can this be done?
>I would appreciate any help I can get. Thanks
>
>