Updating cells based on data validation list

Hi all!

I have done a fair bit of searching on the forum to try and answer my question, do if it has been answered already please accept my apologies for a double post.

What I am trying to do would appear to be very simple. I am trying to build a quotation form for our sales guys to use. I would like to use a drop down list with the list of products in, which I have created using a list in a second sheet and then using a data validation drop down list.

What I would like to achieve is the sales person selects the product (of a list of only 14) from the list and excel to automatically fill in the two columns to the right with the model no. and also the price.

Would it be possible to use an IF statement as there are only 14 products to choose from? Is there a better way of doing this? I have created the lists of model numbers and prices in the second sheet alongside the product name which the drop down list sources from.

Any pointers on how to do this would be much appreciated.

Kind regards

Ben

g'day Ben

I run a similar system if you could post your worbook I would be happy to help and you're right it is simple.

Copy this into B58 and copy and paste down

=VLOOKUP(C58,parts2,2)

Sorry forgot this.

Do the same with this in E58

=VLOOKUP(C58,'List Options'!A1:C13,3)

HTH

Apologies E58 should be

=VLOOKUP(C58,'List Options'!$A$1:$C$13,3)

Sorry mate having a shocker, ignore all previous correspondance.

In B58 copy: =IF(C58="","",VLOOKUP(C58,parts2,2,FALSE)) and paste down

In E58 copy: =IF(D58>0,VLOOKUP(B58,'List Options'!$B$1:$C$14,2,FALSE),0) and paste down

Bang on.

Thanks ever so much Tony! Took me a couple of tries to get it to work but now its working perfectly, exactly what I wanted.

Thanks again buddy!

Ben

If this is a template it might be a good idea to protect the cells with formulas in them and/or put a macro in to reset the user entries to zero, then do a save as under the customer reference.

HTH

Tony

I had thought of this, as you can probably tell the user of this template isn't gonig to be particularly au fait with excel, and as such I agree with you that there will be some cell locking to be done!

As it is going to be a template it will be read only anyway, forcing the user to Save As prior to even looking up the client data.

I am working on a macro now which will search through the quote table, and in the event that not all 14 lines are used willl detect rows with no item selected from the drop down box, delete the row and then add in a new row at the bottom of that page so that the succeeding three pages do not fall out of alignment.

This probably isn't the right forum for this, so if admin wants to move it then I apologise in advance, however the code I have put together so far is this code:

Please Login or Register  to view this content.

however it delets the rows WITH data in rather than the ones without.

I presume that into the loop, to insert a line at the bottom of that page (row 79) I would use something like

Please Login or Register  to view this content.

before "Next i"

Ahhhhhhhhhh it's been so long since I have done all of this, since school in fact!

Whats your take on this Tony?

Are you doing this just for appearance sake?

Is this the form the salesman uses to do all the pricing or only the form you want the customer to see?

If you could explain your percieved final outcome i may be able to help further. will be away for a couple of days but will have a look when i get back