+ Reply to Thread
Results 1 to 11 of 11

Dropdown menu to affect a cell's formula

  1. #1
    Registered User
    Join Date
    08-28-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    14

    Question Dropdown menu to affect a cell's formula

    Hi, I've been making an online price quote calculator to be uploaded to the web, but am stood at a standstill.

    I want my dropdown menu's list " 1, 2, 3, 4, 5, 6" to affect my total cost formula. (I am using data validation, but also own the Excel add-on Spreadsheet Converter)

    My total cost formula is a simple =AC26 which is the total outcome of how much Item 1 would cost.

    I want to be able to select Item 2 and the total cost formula to be changed to =AH26

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Dropdown menu to affect a cell's formula

    Hi,

    Try this formula (assume the drop down cell is A1):
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Hope this helps

  3. #3
    Registered User
    Join Date
    08-28-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Dropdown menu to affect a cell's formula

    Hi, thanks for the response.

    where do i input this formula?

  4. #4
    Registered User
    Join Date
    08-28-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Dropdown menu to affect a cell's formula

    also, I'm trying to make the selection of the dropdown change the formula of a cell, not have it's value.

    so.. for example

    the actual dropdown has the values options 1, 2, 3, 4, 5.

    I want option 1 to change cell D11 ' =AC26 '

    and option 2 to change cell D11 to ' =AH26 '

  5. #5
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Dropdown menu to affect a cell's formula

    Hi,

    Yes my formula will change the formula of the cell D11 dependent upon the value of the drop-down.

    So change "A1" in my formula to be the cell that your drop-down list resides in, and enter the formula in cell D11 if that is the cell who's formula you are trying to change.

    If this doesn't work for you, please upload your workbook so I can take a look.

    Thanks

  6. #6
    Registered User
    Join Date
    08-28-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Dropdown menu to affect a cell's formula

    I attached my workbook, the cells I need help with are.

    Dropdown menu - A5 (Is merged, if that gives you issues feel free to unmerge and fix.)

    Grand Total - D15 (Is merged, if that gives you issues feel free to unmerge and fix.)

    and the actual cells that include the formulas i want grand total to be are:
    1 2 3 4 5 6
    AC26 AH26 AM26 AR26 AW26 BB26

    Price Calcutlator.xlsxHere's my workbook.

    Thanks again :D

  7. #7
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Dropdown menu to affect a cell's formula

    Hi,

    Test out the formula in D15 and let me know if this is what you wanted
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-28-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Dropdown menu to affect a cell's formula

    That's exactly what i need thanks again :D

    One more question,

    you can see that the material, adhesive, and coating dropdowns have not been formulated, is it possible for the choices to have a value and also change a cells value.

    For example

    i want the material drop that has these choices

    Material
    Value
    semi-gloss paper - .32
    matte paper .38
    white bopp .60
    clear bopp .57

    And i want them to change the value in cell AC3

    thanks again man!

    I tried
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Adriandiaz101; 08-30-2013 at 03:48 PM.

  9. #9
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Dropdown menu to affect a cell's formula

    No problems!

    It sure is possible, and you were very close too! Enter this formula in cell AC3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Hope this helps

  10. #10
    Registered User
    Join Date
    08-28-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Dropdown menu to affect a cell's formula

    Got it thanks a lot man.

  11. #11
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Dropdown menu to affect a cell's formula

    No worries!

    Please don't forget to mark this thread as solved, and please click on the * next to my post to say thanks

+ 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. [SOLVED] Excel 2007 : new drop down menu based on dropdown menu in previous cell
    By martinpols in forum Excel General
    Replies: 3
    Last Post: 04-30-2012, 02:33 AM
  2. Code to have dropdown data affect adjacent cell
    By stickingissues in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-15-2012, 12:09 PM
  3. Dropdown Menu with formula
    By MysticGenius in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-15-2011, 06:43 AM
  4. Using Formula to activate a dropdown menu
    By petenickless in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-03-2007, 12:04 PM
  5. [SOLVED] Menu customizations affect all workbooks?
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-06-2005, 11:05 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