Link to home
Create AccountLog in
Avatar of Curtis Long
Curtis LongFlag for United States of America

asked on

Excel 2010 Excel quote layout

I have an Excel template for a quote that I would like to use some data validation on.

I have attached a sample sheet.

I would like to set up this sheet so when my salesman selects the drop down on the column "Item" and selects the value there of 10-20-2 it will automatically look on the page called "Data" and fill in the description for that item and its cost.  

Or he can select the drop down on the column "Description" and it will automatically input the item number and cost.

Is there a way to do this??

Thanks!!
example-quote.xlsx
Avatar of Steve
Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

To have the cells with data lists auto-fill you would most likely need to apply some VBA to lookup to the data and return the info, is this what you wanted, or are you trying to achieve a non-macro solution?
Avatar of Curtis Long

ASKER

I really dont care how I do it.  VBA would be fine if you can show me how to set it up.
Use the following formula in B20:

=IF(ISBLANK(A20)=FALSE,VLOOKUP(A20,Data!$A$1:$C$13,2, FALSE),"")

Use the following formula in D20:

=IF(ISBLANK(A20)=FALSE,VLOOKUP(A20,Data!$A$1:$C$13,3, FALSE),"")

You can copy and paste B20 and D20 down the rest of the the sheet and the A20 value will update accordingly.

If you have any other questions let me know.

Brad
ASKER CERTIFIED SOLUTION
Avatar of Steve
Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
That works except when I select the description it doesnt pull the data.
What do you mean by "doesn't pull the data"? Which data?

I have just downloaded the file to a diferent PC and it seems to work fine.
The prices are calculations, have you cleared those cells of their formula?

Could you save teh file and re-post it and I will see if I can work out the issue.
Im so sorry, I must has posted my comment when you did.  I didnt see the additional post till now.  

It works great.

Thanks so much!!

Can you tell me how it works so I can implement the rest of my parts to it??
Also, If the data from the tab "Data" was actually in a table in SQL 2008 R2 how would you access it??

So the following would be set:

Database: TS
Table:  Item_List
Fantastic!!  

I figured out how everything works.

I will repost the question about the SQL table.  

Thanks ever so much!!

A+++++++++