Formula to post data in another spreadsheet

[Guest]

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

 

[Guest]

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¡¡¡

 

[Guest]

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!B27,1,)"")

thanks

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¡¡¡

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

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!B27,1,)"")

thanks

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¡¡¡

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

 

[Guest]

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.

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

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!B27,1,)"")

thanks

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¡¡¡

:

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
(e-mail address removed)