+ Reply to Thread
Results 1 to 13 of 13

Multiple formula options in drop down list

  1. #1
    Registered User
    Join Date
    12-13-2018
    Location
    Christchurch, New Zealand
    MS-Off Ver
    16.20 (most recent - 2018)
    Posts
    5

    Multiple formula options in drop down list

    Hi there,

    I'm fairly new to Excel - can you somehow put formulas in a drop down list?

    I'm trying to give the option of either averaging a value over 12months or just one month (with the option of choosing what month the value goes into) - image below.

    Is this possible to do a drop down list, where the user can type in a value, and choose either a month or to average the value over 12 months?

    Any help appreciated!

    Screen Shot 2018-12-14 at 9.47.42 AM.png

  2. #2
    Registered User
    Join Date
    12-13-2018
    Location
    Christchurch, New Zealand
    MS-Off Ver
    16.20 (most recent - 2018)
    Posts
    5

    Re: Multiple formula options in drop down list

    Workbook attached
    Attached Files Attached Files

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Multiple formula options in drop down list

    when you say "choose ... a month" what does that mean?
    What you describe is possible but you might need to better define what you want.

    removed that part about uploading a sample.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Multiple formula options in drop down list

    ok i think i understand what you mean. You are going to need to add a helper column to this worksheet, probably inbetween column B and C. Is that ok?

  5. #5
    Registered User
    Join Date
    12-13-2018
    Location
    Christchurch, New Zealand
    MS-Off Ver
    16.20 (most recent - 2018)
    Posts
    5

    Re: Multiple formula options in drop down list

    By choose a month I mean, choose to enter the total value into one cell.

    When someone enters a value, say $20,000 into C29, it divides over 12 months. I want to make an option where they can choose to either divide the total value by 12 (as what is happening currently) OR to just put that value as a sum into one month (and choosing the month, whether that be April, Jun, August...)

    Essentially, some expenses listed get averaged over 12 months, or they get the total sum put into just one month.

    Hope this makes sense?

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Multiple formula options in drop down list

    well I'm a bit confused by your post #5, you cannot put a value in a cell and a formula. If you enter a value in a cell it will over write the formula. Is that what you are wanting?

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Multiple formula options in drop down list

    can you somehow put formulas in a drop down list?
    Probably not ... well, not, as far as I know. What you could do is have a list of months and an additional option of, say "All", or "12 month", or something like that.

    You'd then need to structure your formula to use the option selected in the dropdown. The only complication is the All option ... but it shouldn't be that difficult. First, test if All has been selected and use a simple Average function, and for the other half, use an AverageIf function.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  8. #8
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Multiple formula options in drop down list

    ok so i made this up very quickly.
    I added a tab to use for the data validation.
    I also added a column C which has drop downs for either Average or each month.
    You just put the amount in column D and select which month it should go in or Average if you want it split between 12 months. Columns F:Q will automatically update.
    Row 19 has a circular reference because i accidentally went over whatever your formula was in there. I highlighted it in red so you could change that row back.

    Let me know if you have questions.

    PS: I only put the formula in your cost of sales and Overhead rows. Pretty much the rows that just had the simple /12 formula and were not colored in yellow.
    Attached Files Attached Files
    Last edited by dosydos; 12-13-2018 at 05:34 PM.

  9. #9
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Multiple formula options in drop down list

    whoops i apoligize. Ignore the first document. It needed to be saved as an .xlsx i guess to save the data validation. Now you should be able to click on any cell in column C to bring the drop down menu for that row.
    Also just realized you didnt use full month names for row 5. You can either change row 5 to use full namess (April instead of Apr) or you can go to DATA tab and change the names listed there to your abbreviated forms.)
    Attached Files Attached Files
    Last edited by dosydos; 12-13-2018 at 05:40 PM.

  10. #10
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Multiple formula options in drop down list

    Ok, my OCD made me update the data table to use your abbreviated months. Should be all set now.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-13-2018
    Location
    Christchurch, New Zealand
    MS-Off Ver
    16.20 (most recent - 2018)
    Posts
    5

    Re: Multiple formula options in drop down list

    Ah yes this is what I'm looking for! Still somewhat new to excel...so bare with me haha!

    I can see this works for the averaging - still playing around with the formula to get it into the month only when the month is selected.

    For some reason it works on some months and not others? Does this occur on your end as well?

  12. #12
    Registered User
    Join Date
    12-13-2018
    Location
    Christchurch, New Zealand
    MS-Off Ver
    16.20 (most recent - 2018)
    Posts
    5

    Re: Multiple formula options in drop down list

    Quote Originally Posted by dosydos View Post
    Ok, my OCD made me update the data table to use your abbreviated months. Should be all set now.
    Oh wow - this is perfect! Exactly what I am after - thank you so much for your help!! Was going out of my mind trying to figure it out...knew there would be a simple way - didn't even occur to me to use if statements.

  13. #13
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Multiple formula options in drop down list

    Glad this worked for you. I created something similar to this for a smaller business as well, before making the switch to an access database. thanks for the rep

+ 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. Multiple Options in Dependent Drop Down List Box
    By Max_excel in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-18-2021, 06:36 PM
  2. Help with making a drop down list in many cells that allows me to select multiple options
    By translatorinscotland in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-04-2016, 12:53 AM
  3. Select multiple options from drop down list
    By forestview in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-26-2016, 01:42 PM
  4. Select multiple options from drop down list
    By nsprasad in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-29-2015, 05:30 AM
  5. [SOLVED] Printing Multiple Options from a Drop-Down List
    By JayPear in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2014, 10:07 PM
  6. [SOLVED] Column B drop down list with 2 options, columns C & E with dependent drop down lists
    By betic in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-26-2012, 03:47 PM
  7. [SOLVED] My Excel drop-down list eliminates from list options chosen. Help
    By Sybil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2006, 05:25 PM

Tags for this Thread

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