Looking up a value in a different file
 

I have a column in one spreadsheet (Call it "Orders") into which I enter a
book name that I sell, a second column into which I enter a quantity, and a
third column into which the price is entered by looking up the book and
quantity in a different spreadsheet (call it "Pricing") in a different file,
finding the book's price, multiplying the quantity by the price, and
inserting that into the cell containing the lookup function. I don't know how
to write that formula and would appreciate help.

Assumptions:

1) Workbook1.xls Sheet1 contains your pricing, and that Column A
contains the book name and Column B contains the price.

2) Workbook2.xls Sheet1 contains your orders, and that Column A contains
the book and Column B the quantity.

Formula:

[Workbook2.xls]Sheet1!C1, copied down:

=B1*SUMPRODUCT(--('[Workbook1.xls]Sheet1'!$A$1:$A$100=A1),--('[Workbook1.
xls]Sheet1'!$B$1:$B$100))

Hope this helps!

In article ,
"Watercolor artist" <Watercolor
wrote:

I have a column in one spreadsheet (Call it "Orders") into which I enter a
book name that I sell, a second column into which I enter a quantity, and a
third column into which the price is entered by looking up the book and
quantity in a different spreadsheet (call it "Pricing") in a different file,
finding the book's price, multiplying the quantity by the price, and
inserting that into the cell containing the lookup function. I don't know how
to write that formula and would appreciate help.

In C2 maybe something like this...........

=VLOOKUP(A2,'C:\[Pricing.xls]Sheet1'!A:B,2,FALSE)*B2

You may have to adjust the Path, and SheetName and Range accordingly
Vaya con Dios,
Chuck, CABGx3



"Watercolor artist" <Watercolor wrote in
message ...
I have a column in one spreadsheet (Call it "Orders") into which I enter a
book name that I sell, a second column into which I enter a quantity, and
a
third column into which the price is entered by looking up the book and
quantity in a different spreadsheet (call it "Pricing") in a different
file,
finding the book's price, multiplying the quantity by the price, and
inserting that into the cell containing the lookup function. I don't know
how
to write that formula and would appreciate help.

Muchas gracias, Chuck. Your solution worked perfectly.

Howard

"CLR" wrote:

In C2 maybe something like this...........

=VLOOKUP(A2,'C:\[Pricing.xls]Sheet1'!A:B,2,FALSE)*B2

You may have to adjust the Path, and SheetName and Range accordingly
Vaya con Dios,
Chuck, CABGx3



"Watercolor artist" <Watercolor wrote in
message ...
I have a column in one spreadsheet (Call it "Orders") into which I enter a
book name that I sell, a second column into which I enter a quantity, and
a
third column into which the price is entered by looking up the book and
quantity in a different spreadsheet (call it "Pricing") in a different
file,
finding the book's price, multiplying the quantity by the price, and
inserting that into the cell containing the lookup function. I don't know
how
to write that formula and would appreciate help.

Domenic,

Thanks for trying to help. I couldn't get your formula to work in my
situation, but I was able to adapt Chuck's.

Howard

"Domenic" wrote:

Assumptions:

1) Workbook1.xls Sheet1 contains your pricing, and that Column A
contains the book name and Column B contains the price.

2) Workbook2.xls Sheet1 contains your orders, and that Column A contains
the book and Column B the quantity.

Formula:

[Workbook2.xls]Sheet1!C1, copied down:

=B1*SUMPRODUCT(--('[Workbook1.xls]Sheet1'!$A$1:$A$100=A1),--('[Workbook1.
xls]Sheet1'!$B$1:$B$100))

Hope this helps!

In article ,
"Watercolor artist" <Watercolor
wrote:

I have a column in one spreadsheet (Call it "Orders") into which I enter a
book name that I sell, a second column into which I enter a quantity, and a
third column into which the price is entered by looking up the book and
quantity in a different spreadsheet (call it "Pricing") in a different file,
finding the book's price, multiplying the quantity by the price, and
inserting that into the cell containing the lookup function. I don't know how
to write that formula and would appreciate help.

Chuck,

What if the lookup price was in Column C rather than B and that I wanted to
ignore what's in B?

Also, in "A:B,2,FALSE," what exactly do those parameters mean?

Howard

"CLR" wrote:

In C2 maybe something like this...........

=VLOOKUP(A2,'C:\[Pricing.xls]Sheet1'!A:B,2,FALSE)*B2

You may have to adjust the Path, and SheetName and Range accordingly
Vaya con Dios,
Chuck, CABGx3



"Watercolor artist" <Watercolor wrote in
message ...
I have a column in one spreadsheet (Call it "Orders") into which I enter a
book name that I sell, a second column into which I enter a quantity, and
a
third column into which the price is entered by looking up the book and
quantity in a different spreadsheet (call it "Pricing") in a different
file,
finding the book's price, multiplying the quantity by the price, and
inserting that into the cell containing the lookup function. I don't know
how
to write that formula and would appreciate help.

In article ,
"Watercolor artist"
wrote:

What if the lookup price was in Column C rather than B and that I wanted to
ignore what's in B?

=VLOOKUP(A2,'C:\[Pricing.xls]Sheet1'!A:C,3,FALSE)*B2

Also, in "A:B,2,FALSE," what exactly do those parameters mean?

Have a look at the help menu for a detailed explanation.

Hope this helps!

By Help Menu, do you mean the F1 help in Excel? If so, that didn't answer the
questions I'm now asking.

"Domenic" wrote:

In article ,
"Watercolor artist"
wrote:

What if the lookup price was in Column C rather than B and that I wanted to
ignore what's in B?

=VLOOKUP(A2,'C:\[Pricing.xls]Sheet1'!A:C,3,FALSE)*B2

Also, in "A:B,2,FALSE," what exactly do those parameters mean?

Have a look at the help menu for a detailed explanation.

Hope this helps!

Then use..........
=VLOOKUP(A2,'C:\[Pricing.xls]Sheet1'!A:C,3,FALSE)*B2

A:C means for Excel to look up A2 in the Range of column A to column C,
(actually only lookup in column A, but capturing columns B and C for data
retrieval).........this can be limited if you wish to something like
A1:C100, ...............the "3", means that when Excel finds a matching
Value in column A, to step over to the right 3 columns and return the value
in that cell...............FALSE means to provide a return only if an exact
match is found, otherwise an error message.

hth
Vaya con Dios,
Chuck, CABGx3




"Watercolor artist" wrote in
message ...
Chuck,

What if the lookup price was in Column C rather than B and that I wanted
to
ignore what's in B?

Also, in "A:B,2,FALSE," what exactly do those parameters mean?

Howard

"CLR" wrote:

In C2 maybe something like this...........

=VLOOKUP(A2,'C:\[Pricing.xls]Sheet1'!A:B,2,FALSE)*B2

You may have to adjust the Path, and SheetName and Range accordingly
Vaya con Dios,
Chuck, CABGx3



"Watercolor artist" <Watercolor wrote
in
message ...
I have a column in one spreadsheet (Call it "Orders") into which I
enter a
book name that I sell, a second column into which I enter a quantity,
and
a
third column into which the price is entered by looking up the book
and
quantity in a different spreadsheet (call it "Pricing") in a different
file,
finding the book's price, multiplying the quantity by the price, and
inserting that into the cell containing the lookup function. I don't
know
how
to write that formula and would appreciate help.