Dropdown behaviour shows different data depending on selection

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
141
I don't think I have been very clear about what I am looking for in each thread as I have confused you. What I said in my last post is true. When I talk about combining data in the other thread, I mean inserting different outputs into one cell so that it reads as a sentence.

In this one, I started off looking at dropdown lists and moved on to check boxes. Now I have got to the point where I am looking at dropdown lists again because I want to hide the different discount rows until one is selected from a dropdown list, and I always want it to appear in the cells next to the rows relating to a full charge. It should only ever show one row so that I don't end up with lots of data horizontally, avoiding the need to scroll right.

I only have one check box so I don't know what the problem is.
 

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
141
I do need to develop this with what I explained in my previous post but have come across a problem and need to work backwards first.

There is a problem with the calculation. I tested it by inputting a total charge and the end amount should match up, but it doesn't. Do you understand why?

The number of days displayed is correct.

Annual Charge£2,517.61
Start Date01/04/2019
End Date01/04/2020
Inclusive End Date
No
Number of Days
366​
Charge for Period£2,510.731284
£2,510.73
 

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
141
Hello Jason, you've been extremely helpful with this thread and it looks like only you know the answer to my question.

You've probably been really busy and not had a chance to reply and if that's the case, I'm sorry for this post.

Just making sure you've seen my last post about the calculation being wrong.

Thank you so much for everything.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
I've had a look at the formula to try and figure out why the amounts were not adding up correctly, I think that this should resolve that (in C9)

=IF(ISBLANK(C6),"",SUMPRODUCT(C3/(365+(DAY(DATE(YEAR(CHOOSE({1,2},C5,C6-B7))+(MONTH(CHOOSE({1,2},C5,C6-B7))>3),2,29))=29))*CHOOSE({1,2},((C6-B7)-C5)-((C6-B7)-MIN(C6-B7,DATE(YEAR(C5)+(MONTH(C5)>3),3,31)))+1,(C6-B7)-MIN(C6-B7,DATE(YEAR(C5)+(MONTH(C5)>3),3,31)),0)))

I'll go over the rest of the thread tomorrow when I have more free time to see if I can figure out what you need for post #41.

Not sure what the issue is with the checkbox not hiding though. First 2 thoughts there

1. is the checkbox name correct in vba? just because you only have 1 checkbox, that doesn't mean it will be called checkbox 1. (although I think that this should cause a runtime error rather than doing nothing).

2. Have you used any other vba / macros since the checkbox hiding code last worked? Have you encountered any runtime errors (pop up with 'Debug' and 'End' buttons).
Choosing to 'End' while Events are disabled would cause the checkbox hiding code to fail. If this is the case, then it needs to be reactivated with the line
VBA Code:
Application.EnableEvents = True
in the 'Immediate Window' Which is opened by pressing Alt f11 (in excel) to open the vba editor, then pressing Ctrl g.

See if that gets you anywhere on the first couple of problems.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
Having read through the whole thread (again) I'm still convinced that what you're asking for with the dropdown is closer to your other thread than this one.

Ths link will show you how to set up the dropdown, Excel Drop Down Lists - Data Validation

Once you have added it to your sheet, change the layout so that it looks how you want it to, then upload that and post a link to it here. That will avoid any further confusion as to what is required.
 

Forum statistics

Threads
1,078,309
Messages
5,339,410
Members
399,302
Latest member
Swiftymj

Some videos you may like

This Week's Hot Topics

Top