Looking up prices
I will try to make this easy to understand, or atleast as much as possiable.
What I am trying to do is make a Sales Quote for send to customers. I have most of what I am wanting to do done on it. But the one thing that I would like to do, I haven't quite figured out yet. Basicly, I want to make my life easier when I do a quote. Right now I have to open another spreadsheet with my prices and look everything up. So, what I want to do is for me to just type in an item number and excel goes and looks it up. Either in the same book or different one, it doesn't matter. And fill in the price's where it needs to be filled in at. Hopefully I have made it clear enough to understand what I am asking for. Thanks, |
Looking up prices
Create a table in Columns A & B on a w/sheet (say Sheet2):
Column A has item Column B Price of item Let's say you type your item munber in cell A2 on Sheet1 and you want Price in B2 in B2: =VLOOKUP(A2,Sheet2!A1:B100,2,0) Change range (1:100) to suit this will return the price. You might consider entering the item using Data Validation. If you do, set Allow: List Source: =Sheet2!A1:A100 which points to the Items in your price table. Saves both typing and typing errors. HTH "BEH1230" wrote: I will try to make this easy to understand, or atleast as much as possiable. What I am trying to do is make a Sales Quote for send to customers. I have most of what I am wanting to do done on it. But the one thing that I would like to do, I haven't quite figured out yet. Basicly, I want to make my life easier when I do a quote. Right now I have to open another spreadsheet with my prices and look everything up. So, what I want to do is for me to just type in an item number and excel goes and looks it up. Either in the same book or different one, it doesn't matter. And fill in the price's where it needs to be filled in at. Hopefully I have made it clear enough to understand what I am asking for. Thanks, |
Looking up prices
I already have a another book for price already done. But I try this option.
Thanks "Toppers" wrote: Create a table in Columns A & B on a w/sheet (say Sheet2): Column A has item Column B Price of item Let's say you type your item munber in cell A2 on Sheet1 and you want Price in B2 in B2: =VLOOKUP(A2,Sheet2!A1:B100,2,0) Change range (1:100) to suit this will return the price. You might consider entering the item using Data Validation. If you do, set Allow: List Source: =Sheet2!A1:A100 which points to the Items in your price table. Saves both typing and typing errors. HTH "BEH1230" wrote: I will try to make this easy to understand, or atleast as much as possiable. What I am trying to do is make a Sales Quote for send to customers. I have most of what I am wanting to do done on it. But the one thing that I would like to do, I haven't quite figured out yet. Basicly, I want to make my life easier when I do a quote. Right now I have to open another spreadsheet with my prices and look everything up. So, what I want to do is for me to just type in an item number and excel goes and looks it up. Either in the same book or different one, it doesn't matter. And fill in the price's where it needs to be filled in at. Hopefully I have made it clear enough to understand what I am asking for. Thanks, |
Looking up prices
Thanks again, with a little modifaction it worked like a charm. This is
exactly what I needed. I not sure to do data validation at this moment but I will give it a try. Thanks again "BEH1230" wrote: I already have a another book for price already done. But I try this option. Thanks "Toppers" wrote: Create a table in Columns A & B on a w/sheet (say Sheet2): Column A has item Column B Price of item Let's say you type your item munber in cell A2 on Sheet1 and you want Price in B2 in B2: =VLOOKUP(A2,Sheet2!A1:B100,2,0) Change range (1:100) to suit this will return the price. You might consider entering the item using Data Validation. If you do, set Allow: List Source: =Sheet2!A1:A100 which points to the Items in your price table. Saves both typing and typing errors. HTH "BEH1230" wrote: I will try to make this easy to understand, or atleast as much as possiable. What I am trying to do is make a Sales Quote for send to customers. I have most of what I am wanting to do done on it. But the one thing that I would like to do, I haven't quite figured out yet. Basicly, I want to make my life easier when I do a quote. Right now I have to open another spreadsheet with my prices and look everything up. So, what I want to do is for me to just type in an item number and excel goes and looks it up. Either in the same book or different one, it doesn't matter. And fill in the price's where it needs to be filled in at. Hopefully I have made it clear enough to understand what I am asking for. Thanks, |
All times are GMT +1. The time now is 04:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com