Looking up a value in a different file

[Guest]

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]

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!

 

[CLR]

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

 

[Guest]

Muchas gracias, Chuck. Your solution worked perfectly.

Howard

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

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.

 

[Guest]

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

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!

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.

 

[Guest]

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

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

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]

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!

 

[Guest]

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

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!

 

[CLR]

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

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

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



message news:[email protected]...

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.