Evening all,
I have a question regarding the use of drop down menus and having formulas as well as cell values change based on the selection from the menu. I've attached an example sheet with separate tables each showing what the results i want when a certain value from the drop down menu is selected. I want to be able to apply the specific settings to one table based on that one value from the drop down menu.
Thanks in advance!
As a start, I created a range (called units) from your 2nd sheet. Then I used data validation to create the drop-down.
As you didnt include any data, or what you want to do with it, I couldnt take the solution much further yet.
let me know if this is the direction you want, and if so, maybe supply some data for me to play around with, to help you futher along?
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
I'm sorry i should have specified that I have the drop down menu already in place on the first two tables in column N.
Please see attached.
The method I suggested requires that the drop-down menu reference the new range I created, thats where teams already selected get excluded. Im not changing your drop-downs, just the range they refence. It isnt hard to change the refences, if you are using 2007, go to formulas/name manager, select the range and change the reference
Last edited by FDibbins; 03-21-2012 at 08:49 PM.
Thanks for the reply FDibbins, however I'm not sure I'm following what you're saying. I looked at the sheet you attached and where you insterted a Dropdown Menu is where I would manually type in an activity ie. 8" masonry or horizontal reinforcement. Once I've put in an activity then I would go to the Unit column where the dropdown Menu is and select the unit type and then based on the unit I selected it would produce the appropriate formulas and titles.
By the way I wouldnt use two seperate units on one sheet as that would effect the titles and formulas. For example, I would use a sheet for 8" Masonry and then a seperate one for Reinforcement because masonry would be counted and calculated in terms of number of blocks while reinforcement would be calculated in tons with a different set of calculations.
Thanks again!
ok, if I understand you now...you want to use the dropdown to populate N22. After that, what to you want to happen?
I thought you would want a dropdown in A22, then N22 could be made to auto-populate based on A22's value, and you would have the added benifit of standard entry of your "activity". You could then also use that dropdown value to select price, plus other values perhaps? Im sure that you have multiple things that are measured in tons, or sq feet or eaches. You could tie those all together with a standard list of activities, each with its unique unit of measure...enter the activity, the unit populates, the price populates...maybe any special instructions...polulates
let me know what you think please?
FDibbins,
I think we're getting closer to a solution. I've attached another example. On the sheet titled "Drop Down List" you will see the headings I want associated with the "Unit" column as well as the formulas associated with the calculation for each unit. I tried using a vlookup and it worked well for the headings but I wont be able to use it to populate the formulas because each cell would need the appropriate vlookup formula in it and some of the cells would have to be filled in order to calculate.
In regards to a standard set of activities that wouldn't be feasible for me because this is for a construction estimate and there is no standard really when it comes to activities.
I feel pretty confident that the attached sheet will explain better what I'm trying to accomplish.
Thanks for the help, I really appreciate it!
I just realized, there's also an issue with the formulas on the "Drop Down List" Sheet. while they are correct I would need a way for them to adjust appropriately to their respective rows so that the calculations make sense.
In row 22 on drop down list sheet, what should the headings in E22-I22 be? My reason for asking is that there may be a way to write your formulas so that they reference the same columns, that way you have consistent formulas, and that may simplify what you are trying to do?
I only used Row 22 so that I could keep track of where formulas went. The headings I want to use are the ones in rows 2-19 and the formulas associated are in 23-40.
Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks