Dropdown behaviour shows different data depending on selection

[TheJay]

FIrst of all, apologies for the title, I don't know how to change it and realised I wanted to use a tick box instead of a drop down as I was writing.

I have a spreadsheet which calculates the charge for a date range.

Now I want to introduce a tick box in C6, which changes whether the inclusive or exclusive end is shown in C7 and relevant data in C8. As well as the data changing, when the tick box is clicked, I'd like the text in C6 to change from 'Exclusive End' to 'Inclusive End'.

The idea is that I don't want anything in row D because I need to incorporate other data eventually in that row. Not sure how this is achieved, can it be done without hidden cells?

The forum wont let me attach the spreadsheet

 

[TheJay]

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]

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 Date	01/04/2019
End Date	01/04/2020
Inclusive End Date	No
Number of Days	366​
Charge for Period	£2,510.731284
	£2,510.73

 

[TheJay]

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]

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

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]

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.