Query questions (I think)

[Guest]

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.

 

[Guest]

Hi
1. Have a look at VLOOKUP. See:
http://www.contextures.com/xlFunctions02.html

 

[Andy Brown]

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

 

[Brian]

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]

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