Start Date and End Date selection for chart.

You don't need to venture into the world of VBA for this. You could create "dynamic named ranges" based on start and end dates in cells and have the chart automatically update from those.

Perhaps if you post some sample data it will be easier to provide you with a working example.

BSB

Originally Posted by BadlySpelledBuoy

You don't need to venture into the world of VBA for this. You could create "dynamic named ranges" based on start and end dates in cells and have the chart automatically update from those.

Perhaps if you post some sample data it will be easier to provide you with a working example.

BSB

As requested previously I have made a mock workbook, it's attached to the previous post.

Originally Posted by DauntlessDS

The chart on sheet 2 only needs to display numbers 1-100 and then how many times that number appears such as how many times did the output reach 50% or 76% etc.

Do you need the 1-6 split or just how many times each 'percentage' appears between two selected dates regardless of the 1-6 aspect?

Originally Posted by BadlySpelledBuoy

Do you need the 1-6 split or just how many times each 'percentage' appears between two selected dates regardless of the 1-6 aspect?

The 1-6 is just number of values there are to collect for additional data each day. The chart only needs Horizontal Axis 1-100 and Vertical Axis Number of times the value appeared.

Originally Posted by BadlySpelledBuoy

OK, is this anywhere near what you're after?

Select the start date in the green cell and the end date in the red cell. (Note, I've set them up so you cannot select an end date that's prior to the start date.)

The number of times each occurrence appears between (and including) those dates will calculate in column J and the chart will populate from there.

I've put the chart on the same sheet. This is for demonstration purposes only so you can easily see it changing when you change the dates. It's a simple enough task to have that show on a separate sheet.

If this is what you need and you want to replicate it in your main workbook then press Ctrl+F3 to bring up the name manager to see how the named ranges work and replicate the formulas in column J. At the moment I have the formulas set to capture any data down to row 5000. You can obviously adjust this to suit your needs but be wary that this method uses SUMPRODUCT and if you reference entire columns with this formula things will become very sluggish very quickly!

You could set the whole thing up to work with dynamic named ranges for the date and data columns too so the formulas would auto adjust to your data.

BSB

That was absolutely perfect! I haven't broke it down yet to try and recreate it myself but it works exactly how I wanted it and I couldn't thank you more!

No problem at all. Happy to help.

I'll keep an eye on the thread in case there are follow up questions.

BSB

And here, just because I think it will be useful, is your example file with the dynamic ranges I mentioned earlier.
This means the ranges will change size as more data is added (or removed) and you do not have to adjust the formulas.

BSB

Originally Posted by BadlySpelledBuoy

And here, just because I think it will be useful, is your example file with the dynamic ranges I mentioned earlier.
This means the ranges will change size as more data is added (or removed) and you do not have to adjust the formulas.

BSB

It doesn't appear to be different?

The differences are all behind the scenes.
Press Ctrl+F3 again and you'll see more dynamic ranges. Then look at the formulas in column J compared to the earlier version.

Try tagging new data on at the end of the list and you'll not need to make any changes to the formulas, even when you get past the 5000 rows that I set it to earlier.

BSB

Originally Posted by BadlySpelledBuoy

The differences are all behind the scenes.
Press Ctrl+F3 again and you'll see more dynamic ranges. Then look at the formulas in column J compared to the earlier version.

Try tagging new data on at the end of the list and you'll not need to make any changes to the formulas, even when you get past the 5000 rows that I set it to earlier.

BSB

Ahhh I see, thank you. I am sorry for not looking at that before posting.

Capture.PNG

So I tried to adjust for letters as well as numbers to use for another workbook for cataloging information alphabetically. The above image shows that when I changed the chart data for the horizontal axis to be 1-26 instead of 1-100, it just mushed it together instead of spreading it across the chart size. What do I need to change for that to happen? I don't it's in the dynamic ranges so I am stumped otherwise.

You need to change the range associated with the chart.
In this workbook the dynamic named ranges are only dealing with the drop downs for start and end dates and the formulas in column J.
There are no dynamic ranges tied to the chart in this case.

So click on the chart until you see coloured outlines round the dates and data then resize them by dragging the bottom left corner of the coloured boxes up to the 26th row from the 100th row.

Hopefully that makes sense.

BSB

Originally Posted by BadlySpelledBuoy

You need to change the range associated with the chart.
In this workbook the dynamic named ranges are only dealing with the drop downs for start and end dates and the formulas in column J.
There are no dynamic ranges tied to the chart in this case.

So click on the chart until you see coloured outlines round the dates and data then resize them by dragging the bottom left corner of the coloured boxes up to the 26th row from the 100th row.

Hopefully that makes sense.

BSB

It does not as it is not in front of me right now. I will open it up when I get back and see what magic is.

Originally Posted by BadlySpelledBuoy

You need to change the range associated with the chart.
In this workbook the dynamic named ranges are only dealing with the drop downs for start and end dates and the formulas in column J.
There are no dynamic ranges tied to the chart in this case.

So click on the chart until you see coloured outlines round the dates and data then resize them by dragging the bottom left corner of the coloured boxes up to the 26th row from the 100th row.

Hopefully that makes sense.

BSB

Worked perfectly good sir but not in the way you described however it led me to what I needed to find. I selected the chart and clicked on the funnel then unchecked everything after 26. EZ-PZ. One last question IF anyone has the time to direct me to that tutorial or terminology as well. I want to enter the data from the top line as it will be arranged Newest to Oldest. Can I just insert new row above and go from there or will that mess up the formulas?