Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 31st 20, 07:56 AM
Junior Member
 
First recorded activity by ExcelBanter: Oct 2020
Posts: 1
Default Converting three calculators in a workbook into one with a dropdown box (hardcoded)

Originally posted on Excel Forum: 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.


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


C67 for No Payments


=IF(ISBLANK(D6),"",(C6*D6))
C10, D10 & E10 for Credit on Account


=IF(ISBLANK(E10),"",(C10+E10))
C14, D14 & E14 for Debit on Account


=IF(ISBLANK(E14),"",(C14*D14-E14))


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


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.


Spreadsheet:WCD EXPORT.xlsm https://drive.google.com/file/d/1olH...ew?usp=sharing


Thank you for your help.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Replacing a hardcoded date John Menken Excel Programming 2 January 13th 12 07:44 PM
use Find without hardcoded value but a value from a cell Richhall[_2_] Excel Worksheet Functions 1 January 5th 10 06:07 PM
where do I find capital leasing calculators? gburtscher Excel Discussion (Misc queries) 1 May 9th 08 02:24 PM
calculators Fay Yocum Excel Worksheet Functions 2 December 10th 06 08:41 AM
How to format number the same way calculators do Stefano Gatto Excel Programming 3 February 16th 06 06:15 PM


All times are GMT +1. The time now is 10:35 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017