+ Reply to Thread
Results 1 to 34 of 34

Converting three calculators on a worksheet into one with a dropdown box

  1. #1
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Converting three calculators on a worksheet into one with a dropdown box

    I have attached a copy of my calculator, which manipulates the figure depending on the data input in various cells.

    The user inputs a number in D6, D10 & E10 or D14 & E14 depending on the situation.

    C6:D7 for No Payments
    Please Login or Register  to view this content.
    C10, D10 & E10 for Credit on Account
    Please Login or Register  to view this content.
    C14, D14 & E14 for Debit on Account
    Please Login or Register  to view this content.
    I want to merge everything so that the user uses the same boxes for each scenario by choosing whether there are no payments, a credit on the account or a debit on the account from a dropdown box that should appear in B5 with those options.

    This would dictate whether there were three headings or four and the formula/calculation/validation used as mentioned above.
    If the user selected no payments, the headings would be Monthly Amount, Instalments and Total.
    If the user selected credit on account, the headings would be Monthly Amount, Instalments, Credit and Total
    If the user selected debit on account, the headings would be Monthly Amount, Instalments, Debit and Total

    Can anyone please help me achieve this?

    Thank you so much for your help in advance.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    595

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Try this - I used Coditional formatting to make your headers change & data validation for your options
    Attached Files Attached Files
    Last edited by CRIMEDOG; 08-21-2020 at 11:02 AM. Reason: Updated solution to remove stray formulas
    If this solves your issue, Mark thread as Solved & Add Reputation.

    Thanks!

  3. #3
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Thanks for your reply crimedog. Is it possible without using the other calculator? I want to keep the worksheet clean. Also, I'm getting the same results no matter what I choose from the dropdown and what figures I input, along with an error in the total cell referring to unrecognised text.

  4. #4
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    595

    Re: Converting three calculators on a worksheet into one with a dropdown box

    It works for me.. put
    Please Login or Register  to view this content.
    under total.
    You can delete the old calculator. Make sure you know what I did with the data validation & how the Conditional formatting is working.

  5. #5
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Thanks, that is working much better. I really appreciate your help.

    How about a way to use the same column and row cell (M6) for either a credit or debit? Even better, if it's not a credit or debit, that row is hidden completely so there isn't a blank cell as the user moves through data entry? Do the options need to appear on a separate worksheet?

    I'm also unsure how to move the dropdown box to J6 without breaking it.
    Last edited by ThiaJay; 08-21-2020 at 05:13 PM.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,893

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Perhaps a couple of adjustments to CRIMEDOG's setup will accomplish what you want.
    The formula for the total is modified to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Cell M5 is populated using: =IF(J6="No Payments","",TRIM(LEFT(J6,6)))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Hello Jete, thanks for your reply.

    I opened the worksheet that you shared and it comes up with an error in the Total cell and says #NAME.

    Also, the M column doesn't appear/disappear depending on the dropdown selection.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,893

    Re: Converting three calculators on a worksheet into one with a dropdown box

    As for the Total cell use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    As for column M disappearing that would require VBA, of which I know too little to help.
    Let us know if you have any questions.

  9. #9
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Thank you, that works better. I'll hope someone with VBA experience can help soon. In the mean time, can the dropdown options be kept to the same sheet and preferably the cell where the dropdown box appears or the one immediately underneath?

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,893

    Re: Converting three calculators on a worksheet into one with a dropdown box

    The options could be put on the same sheet, however as there are three options there would need to be three cells.
    In the attached file I put them out of sight in cells XFD1:XFD3, however feel free to move them.
    Let us know if you have any questions.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Thank you very much.

    Is it possible for the text be moved to a row instead of a column? Why is it that if I remove the calculator on the left and try to move this version the text in the dropdown disappears?

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,893

    Re: Converting three calculators on a worksheet into one with a dropdown box

    It seems that there will only be three options, therefore I suggest hard coding the data validation as modeled in the attached file, so that you can move the calculator without disrupting the list of options.
    Let us know if you have any questions.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Jete, thank you so much. That's exactly what I was looking for.

    Can anyone please help me to figure out the VBA to make row E appear in the worksheet JeteMc just shared when "Credit on Account" or "Debit on Account" is selected from the dropdown and row E disappear if "No Payments" is selected (it also seems to be the default so when the worksheet opens row E should not display initially)?

  14. #14
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Is there anyone who can help with the VBA to make column E disappear according to the selection from the dropdown box as described in my previous post?

  15. #15
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    I've found something similar but don't know how it should be adapted to deal with the dropdown options being hard coded nor the fact there are three options instead of two (yes/no)?

    I think I have figured it out?
    Please Login or Register  to view this content.
    Last edited by ThiaJay; 08-25-2020 at 01:51 PM.

  16. #16
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    I've been testing this VBA and it works well. However, as soon as I lock the worksheet except cells C4:F4, when I choose "No Payments" from the dropdown, I get an error.

    Code:
    Please Login or Register  to view this content.
    Error:
    Run-time error "1004"
    Unable to set the Hidden property of the Range class

    Points to:
    Please Login or Register  to view this content.
    Can someone please help me get this fixed?

    Should I be using something like userinterfaceonly protection? If so, how do I apply this to all cells except C4:F4?
    Last edited by ThiaJay; 08-25-2020 at 04:41 PM.

  17. #17
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    After experimenting, I seem to have messed with something and now a whole range of cells are made invisible when "No Payments" is selected from the dropdown. Any idea why this is?
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Hey Jete, I am hoping you can help me please. I also want to add further behaviours like this but this time dealing with some rows. I'm not sure how you created the dropdown list and I would like to add another?

  19. #19
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Ah, I figured it out. The icon for data validation is tiny in this version of Excel!

    I have it semi-working as required. By default the dropdown should show the text "No Discount", which hides all rows. The problem is, if the user then selects different discount amounts afterwards, it shows each set of rows and I only want it to show one lot of corresponding rows at a time. Can you please tell me what I am missing?

    Please Login or Register  to view this content.
    I want everything hard coded so there aren't any other cell references used other than for the drop down list. These are the options in the list:

    No Discount,25% Discount,50% Discount,50% & 25% Discount

    Thanks
    Last edited by ThiaJay; 09-26-2020 at 05:28 PM.

  20. #20
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    I've been trying without success to move forward with this, if anyone could please fill me in that would be much appreciated. Thank you.

  21. #21
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Can anyone help?

  22. #22
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    I'm hoping someone knows the answer please.

  23. #23
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    5,088

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Pl see file.
    C4 is validated.Validation list J1:J3
    formula in C5 dragged to right Upto F5.
    Formula selects data from L1:O3

    =INDEX($L$1:$O$3,MATCH($C$4,$J$1:$J$3,0),COLUMNS($C$5:C5))
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  24. #24
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Thank you for your reply. I am trying to avoid having extra cells and was looking for a hard coded solution as I said before. I've attached my spreadsheet so you can see my progress.

    Mine works fairly well, except for three issues.

    The first is the way that the debit calculator resets itself to hide a row when "No Payments" is selected, particularly now that the Day & Discount calculator has been introduced. Using the reset buttons causes multiple columns to be hidden incorrectly instead of "Credit" in Cell F10.

    The second is the way that the day and discount calculator doesn't hide the other options when a selection is made. I'm trying to replicate the same type of behavior used with the debit calculator for the column, but with the relevant rows instead (obviously now with the same flaw hiding lots of rows).

    The dropdown arrow also seems to move to the second to last row when the option changes from "No Discount". I want it to stay put.
    Attached Files Attached Files
    Last edited by ThiaJay; 09-30-2020 at 01:29 PM.

  25. #25
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    I have uploaded a slightly modified version to redact data. This should be used instead of the original for any response. Thank you.

  26. #26
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Anyone, please?

  27. #27
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Anyone, please?

  28. #28
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Hoping today someone will notice this that knows how to help.

  29. #29
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box


  30. #30
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Anyone, please?

  31. #31
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    This is the file that you should use in relation to this thread.
    Attached Files Attached Files

  32. #32
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    191

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Hello everyone!

    I have attached a copy of my calculator, which manipulates the figure depending on the data input in various cells.

    C4 & D4, D10, E10 & (depending on dropdown selection) F10, or C16, D16 & E16 (depending on the situation).

    Please Login or Register  to view this content.
    I want to merge everything so that the user uses the same boxes for each scenario by choosing whether there are no payments, a credit on the account or a debit on the account from a dropdown box that should appear in C10 with those options. This is mainly working.

    This would dictate whether there were three headings or four and the formula/calculation/validation used as mentioned above.
    If the user selected no payments, the headings would be Monthly Amount, Instalments and Total.
    If the user selected credit on account, the headings would be Monthly Amount, Instalments, Credit and Total
    If the user selected debit on account, the headings would be Monthly Amount, Instalments, Debit and Total

    The options must be hardcoded, not referring to external cells. It's close to working, but not quite.

    I am trying to avoid having extra cells and was looking for a hard coded solution as I said before. I've attached my spreadsheet so you can see my progress.

    Mine works fairly well, except for three issues.

    Problems:
    The first is the way that the debit calculator resets itself to hide a row when "No Payments" is selected, particularly now that the Day & Discount calculator has been introduced. Using the reset buttons causes multiple columns to be hidden incorrectly instead of "Credit" in Cell F10.

    The second is the way that the day and discount calculator doesn't hide the other options when a selection is made. I'm trying to replicate the same type of behavior used with the debit calculator for the column, but with the relevant rows instead (obviously now with the same flaw hiding lots of rows).

    The dropdown arrow also seems to move to the second to last row when the option changes from "No Discount". I want it to stay put.

    Thank you for your help.

  33. #33
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,453

    Re: Converting three calculators on a worksheet into one with a dropdown box

    Cross post links were added
    Last edited by Pepe Le Mokko; 11-03-2020 at 09:43 AM.
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  34. #34

+ 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. individual retirement account for 25 month calculators
    By lex.luthor in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-21-2020, 12:49 PM
  2. Default Excel Mortgage Calculators into Web
    By gurpreet161 in forum Excel General
    Replies: 0
    Last Post: 02-11-2019, 01:10 AM
  3. Converting Spreadsheets to Online Calculators
    By nohmis in forum Excel General
    Replies: 0
    Last Post: 04-20-2018, 09:44 AM
  4. [SOLVED] Formulas for Target Calculators
    By bdrod in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 03-23-2016, 10:29 AM
  5. Texas Instruments Calculators
    By Lennnny in forum The Water Cooler
    Replies: 2
    Last Post: 12-13-2013, 09:45 PM
  6. Excel with Dropdown and Formula converting to HTML
    By junior123 in forum Excel General
    Replies: 1
    Last Post: 08-14-2012, 11:35 PM
  7. How to format number the same way calculators do
    By Stefano Gatto in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-16-2006, 01:20 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