Hello,
I have a question, I just learned to use drop down lists using validation.
Here is the basic mechanics of my question.
On one tab I have two columns. One is a parts column and adjacent is a corresponding price column.
On another tab i have a drop down list for the part, When i choose my part i want the price for that part to be entered as well.
Is there a simple way to do this?
Thanks in advance I am new to this forum.
Last edited by William_IV; 10-04-2012 at 03:26 PM.
You can have the price enter in the next cell. If your dropdown is in A1, in B1
=IF(ISBLANK(A1),"", VLOOKUP(A1,sheet2!$A$1:$B$200,2,FALSE))
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
ChemistB,
Yep that works, I thought it would have to be some kind of IF statement. I haven't learned VLOOKUP yet, will review the command and play with it. Thanks again!
ChemistB,
Your solution led to an additional bug, perhaps it's just my lack of experience working with formulas.
Here is the problem:
How can i fill down your formula so i could select one of six parts for a hundred rows without having to manually change the formula for each row?
I tried auto filling. but the values in the formula change then i have to manually change them back at every row.
if you are using A1 as your drop-down, , you need to absolute that to copy it down $A$1. but that begs the question...why would you want to? if you use the above formula =IF(ISBLANK(A1),"", VLOOKUP(A1,sheet2!$A$1:$B$200,2,FALSE)), and just absoluted A1, you would get exactly the same answer in every cell you copied it to
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Yep, I'm not sure what you are looking at now.
Are you saying that you have multiple dropdown cells (i.e. A1, A2, A3...)?
Or are you expecting multiple results from a single dropdown (i.e. if A1 = red, there are multiple matches, "apples", "cherries" "strawberries") and you want to return all of them in different cells (much more complex and we'd need to start over)?
or something else?
ChemistB, Your right i should explain better.
I have two sheets
On sheet2 there is two columns, one column (A1thru A100) is parts for construction, for example pipe, elbows, tees,unions. Beside the same column is another column of prices for the part,(B1thru B100). sheet 2 is my template and data base of parts and pricing.
Sheet one is my form for estimating a specific project. Cell A1 is a drop down list of the parts in sheet 2 Acolumn. When I select tees I want the price for the tee entered as well. I might also need to select tees several times depending if the project is broken down.
For example:
Sheet one may look like this:
Tees 2.50
Elbows 1.00
Tees 2.50
Tees 2.50
Tees 2.50
Pipe 3.00
Unions 4.50
Basically i'm looking to select the part and have the price for it returned as well. then when i finish my sheet1 estimate i save that sheet for the project i'm working on, and then start a new estimate for project 2 etc. etc.
Hopefully this is more clear on what i'm trying to accomplish.
Take a look at the attachment. Does this make sense? Is this what you are trying to achieve? Questions?
ChemistB,
Your a Genius! works great!
The first string you gave me at the bigginning of my thread I was unable to grab colum b and pull down the formula without the formula changing on me, therefore I would manually have to change each formula.
Using your attachment I could pull down the formula and extend sheet one to A250 without difficulties. I noticed only the lookup values changed as needed and the rest of the string remained unchanged. Thanks again your spreadsheet was exactly what i was trying to accomplish, I learned a lot from this thank-you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks