Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Query questions (I think)
My situation:
I have a worksheet that is my pricelist. It contains columns representing retail price, my cost, part number, and description. 1) I have another worksheet in my workbook that I want to use for quoting. I would like to be able to simply type in the part number and have the rest of the field auto populate from the pricelist. 2) I would also like to have rows on the quote sheet that don't contain any information to be autohidden (if this is possible). Thank you for your time and attention, greatly appreciated. |
#2
|
|||
|
|||
Hi
1. Have a look at VLOOKUP. See: http://www.contextures.com/xlFunctions02.html "Brian" wrote: My situation: I have a worksheet that is my pricelist. It contains columns representing retail price, my cost, part number, and description. 1) I have another worksheet in my workbook that I want to use for quoting. I would like to be able to simply type in the part number and have the rest of the field auto populate from the pricelist. 2) I would also like to have rows on the quote sheet that don't contain any information to be autohidden (if this is possible). Thank you for your time and attention, greatly appreciated. |
#3
|
|||
|
|||
I have a worksheet that is my pricelist. It contains columns representing
retail price, my cost, part number, and description. 1) I have another worksheet in my workbook that I want to use for quoting. I would like to be able to simply type in the part number and have the rest of the field auto populate from the pricelist. Firstly, re-order your pricelist columns so that part number is the first. Then you can use VLOOKUP, eg: =VLOOKUP(A2,PriceList!$A$2:$D$4,2,FALSE) where A2 = cell on Quote sheet where you entered the part number ; PriceList!$A$2:$D$4 = your pricelist table ; & 2 = the pricelist table column from which you need the info (in this case, the 2nd column, whatever that contains. 2) I would also like to have rows on the quote sheet that don't contain any information to be autohidden (if this is possible). You can do this with a macro, AKA VBA. Or you can select the range (that may contain blanks) and F5 -- Special -- Blanks -- OK, and then Format -- Row -- Hide. HTH, Andy |
#4
|
|||
|
|||
Thanks for the input.
One issue though, I get the pricelist from my vendor and it is protect therefore I am unable to resort the columns, is it a problem to use it as is? Current format is Column A is description, B is part number, C is retail price, D is my price. Thanks again. "Andy Brown" wrote in message ... I have a worksheet that is my pricelist. It contains columns representing retail price, my cost, part number, and description. 1) I have another worksheet in my workbook that I want to use for quoting. I would like to be able to simply type in the part number and have the rest of the field auto populate from the pricelist. Firstly, re-order your pricelist columns so that part number is the first. Then you can use VLOOKUP, eg: =VLOOKUP(A2,PriceList!$A$2:$D$4,2,FALSE) where A2 = cell on Quote sheet where you entered the part number ; PriceList!$A$2:$D$4 = your pricelist table ; & 2 = the pricelist table column from which you need the info (in this case, the 2nd column, whatever that contains. 2) I would also like to have rows on the quote sheet that don't contain any information to be autohidden (if this is possible). You can do this with a macro, AKA VBA. Or you can select the range (that may contain blanks) and F5 -- Special -- Blanks -- OK, and then Format -- Row -- Hide. HTH, Andy |
#5
|
|||
|
|||
Current format is Column A is description, B is part number, C is retail
price, D is my price. Try (in B2 of quote sheet, where A2 = part number): =INDEX(PriceList!A:A,MATCH(A2,PriceList!B:B,0),1) HTH, Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query of External Data | Excel Discussion (Misc queries) | |||
saving wkbk minus MS Query links | Excel Discussion (Misc queries) | |||
Microsoft Query Help | Excel Worksheet Functions | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
Editing MS Query | Excel Worksheet Functions |