Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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! |
#8
|
|||
|
|||
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! |
#9
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) | |||
Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) | |||
Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |