+ Reply to Thread
Results 1 to 10 of 10

Set up worksheet to show/hide sections based on items selected in a dropdown menu

  1. #1
    Registered User
    Join Date
    04-29-2016
    Location
    ireland
    MS-Off Ver
    2016
    Posts
    21

    Set up worksheet to show/hide sections based on items selected in a dropdown menu

    Hi All,

    I have a pricing house renovation document with about 10 pricing sections on the one worksheet.
    All sections are rarely used for each job but no job is ever really the same.

    What I'd like to be able to do is have a drop down menu at the top of the sheet where I can select what items will be used for this job.
    This will then populate the sections I require.

    Another item I'd like to be able to incorporate is where there are multiple types of house on one estate, ie Type A, Type B, Type C, Type D
    Would it be possible to add columns based on how many different house types I require.

    All help is greatly appreciated.

    If required I can send a sample of the existing document.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Set up worksheet to show/hide sections based on items selected in a dropdown menu

    Do attach a sample. It will help us understand the requirement and come up with a solution.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    04-29-2016
    Location
    ireland
    MS-Off Ver
    2016
    Posts
    21

    Re: Set up worksheet to show/hide sections based on items selected in a dropdown menu

    Thanks for the response.

    I'm not sure if the doc is attached or not, I've tried a couple of times but can't see whether or not it is actually attached.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-29-2016
    Location
    ireland
    MS-Off Ver
    2016
    Posts
    21

    Re: Set up worksheet to show/hide sections based on items selected in a dropdown menu

    The ORIGINAL tab is the worksheet I'm currently using.
    All sections are required.
    It's very cluttered and I'm hoping that I can get a bit of advice on how to redesign it to make it a bit more user friendly.

    The STRIPPED DOWN tab shows the list of all possible upgrade items I2:L19
    I think that it might be better to have a check box next to the list of possible upgrade items (I3:I18) rather than a drop down menu as I will need to display the info in I2:L19 anyway.
    If an item isn't selected then it should disappear to clean up the sheet a bit, see the DESIRED OUTCOME tab.

    Something else I'd like to happen is that when you select a work measure (ie on the STRIPPED DOWN tab, cell A37) the corresponding UNIT BASE RATE (D37) changes accordingly.
    How do I go about setting up pricing for each element, could this be done on the MEASURE DATA tab where there are dynamic lists for each of the upgrade measures?

  5. #5
    Registered User
    Join Date
    04-29-2016
    Location
    ireland
    MS-Off Ver
    2016
    Posts
    21

    Re: Set up worksheet to show/hide sections based on items selected in a dropdown menu

    Hi All,

    has anyone had a chance to review the document?

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Set up worksheet to show/hide sections based on items selected in a dropdown menu

    I'm looking at it. What I don't see at the moment is where you are getting your costs. Also I don't see where you get the numbers in columns H:J and why they affect column B. Basically, I am trying to reverse engineer the formulas and determine why they are significant.

    Walk us through a couple of steps and what you expect to see as you type along.

  7. #7
    Registered User
    Join Date
    04-29-2016
    Location
    ireland
    MS-Off Ver
    2016
    Posts
    21

    Re: Set up worksheet to show/hide sections based on items selected in a dropdown menu

    Hi dflak, thanks for the response.

    if you look at the "Original" tab, the following is how it works...I think!!

    There are a number of pricing sections that need to be filled in to generate the total costs.
    They are located in H16:N188

    in the above range, there are sub sections in each pricing section that I would like to have a dynamic range dropdown list (I can do this myself, just about).
    For example, in H26:H39 I would like to be able to select from a list of items that might have to be done in the "Attic" section.
    The light blue "MISCELLANEOUS ITEM - OVERWRITE" cells (H40:H42) would be for items that aren't on the existing dropdown list and would be unique to a house.
    Currently, I then have to input a unit rate into K26:K39
    In range N9:N13 I have to select the quantity of this particular house type, whether it is a house or an apartment (has a knock on effect elsewhere).
    Then, using the Attic section as an example, I input the quantity of each measure from H26:H39 into N26:N39
    Cells I26:I39 have the following formula =SUM($N$11*N26), $N$11 (the quantity of this type of house) multiplied by the quantity of the particular measure gives me the total quantity of the particular measure
    =K26*I26 multiplies the total quantity of the particular measure by the rate to give me the cost of the measure.

    Sorry this is so convoluted.

    I'll go on....

    Each measure (A39:A52) has a pricing section in column H as detailed above.
    If a measure is being completed on a house it is given a set Credit value.
    This is summarised in range A36:E53

    The credit values are in range A56:D90
    As you can see, there are different values depending on whether the building being priced is a house or an apartment, hence the reason for cell N13

    At the moment I have to select "Yes", "No" or "Not Sure" in cells similar to I25 & N25 for each measure, this triggers whether the credits should be applied in range A36:E53

    The total Credits E53 links to D13 to help generate a required value for a report.

    E53 / D13 also feed into a formula (E4) to calculate how much Grant 1 (C4) is worth.

    Range A4:B14 is a financial summary
    Cell B4 is linked to C32 and then the rest of the figures in range B5:B14 are calculated based on this Total Base Cost and the Profit on Base Cost in cell I6

    Hope I haven't just made you purge your account!

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Set up worksheet to show/hide sections based on items selected in a dropdown menu

    I'll try to keep up .

  9. #9
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Set up worksheet to show/hide sections based on items selected in a dropdown menu

    Just a space holder. I am busy at the moment and probably won't get to it until the weekend.

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Set up worksheet to show/hide sections based on items selected in a dropdown menu

    I tried to follow your last post, but none of the cells match your descriptions.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 03-07-2018, 07:31 PM
  2. Hide Columns In Different Worksheet When Dropdown Value Is Selected
    By philwaters in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-21-2016, 11:00 AM
  3. [SOLVED] To hide the checkboxes based on a selection in the dropdown menu
    By Simrat Kaur in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-28-2014, 03:56 PM
  4. Replies: 4
    Last Post: 05-22-2013, 03:23 PM
  5. Replies: 0
    Last Post: 09-13-2012, 03:53 AM
  6. Hide worksheet and show only when selected
    By famico78 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2009, 06:51 AM
  7. Hide/show specific Pivot Table Items based on multiple criteria
    By wotadude in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-18-2009, 05:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1