Creating Dynamic Table or Array

EE Pros,

I have a very nice Macro that several EE Pros helped build that calculates a Interval and Frequency (Date/Time) between a start date that is entered and an end date that is calculated by the existing macro.  

I now need a macro addition that auto/dynamically (i.e. as the selections change) builds out the data table X will be the Interval values and Y1 and Y2 will have data that is manually put into the model.  I believe the best way to then get it into a dynamic graphic is with Range Names that expand and contract based on the selections.  

In the WB provided, I've outlined the 3 Steps that take place.  The first two steps are calculated with the macro that is in place.  The actual creation of a dynamic Table or Array is what I'm after in this new macro.  Careful to take note that as you press the blue button, the format changes for what will be the X axis or Interval(s).

Thank you VERY much in advance.  I know this is a tougher then normal request.

B.
D--Data-Data-Temp-Macro-to-create-D.xlsm
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
In your spreadsheet you have "G8:K" and I assume you mean "G8:K8". Does "G9:9" mean "G9:L9"?
Bright01Author Commented:
Martin,

Greetings!  The reason I put K, instead of K8 is because if in the selection, the Interval is greater or less then 4, the macro and range needs to adapt to the new leangth of the X1 Axis or Interval.   So if I had selected Year and 5 it would have extended out to L8.  So if you were to select Year and 5, it would have automatically extended out to L8.  If I selected Year and 6, it would have extended to L9 (and reflect it in the expanding Range Name).  If I selected Monthly and 4 Intervals, it would have stayed at L8...because it was the Start Month + 4 Months.

Does that help?

B.
Martin LissOlder than dirtCommented:
Yes, let me see what I can do.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Bright01Author Commented:
Martin,  just to give you some additional information, the intent on this is to create a dynamic table, that as the Interval and the Frequency changes, the macro produces a table that also changes according to the start, Interval, Interval #, and Frequency selection. Then those results, can then be used in a graphic.  Due to the complexity of the graphic with a Y1 and potentially a Y2 data set (manually entered), I elected to do this in stages; 1.) get the date/time engine working first; 2.) get the dynamic table working and 3.) apply the range to a specific graphic.  Presently with this request, this current macro has to do with step 2.

The end result is a dynamic table that can quickly adapt to different input scenarios and calculate a gap between two points that when measured, can produce a value proposition.

I hope that helps.
Martin LissOlder than dirtCommented:
Why would there be "48 total cells in Y1 and Y2"? There are 5 years involved and 48 isn't evenly divisible by 5 so it's unclear to me where those 48 cells would be. Can you show a picture of what you want?
Bright01Author Commented:
Martin.... you are correct ( I must have been half asleep).  If you select 5 and Years as the Interval, and Months as the Frequency... you would have 6 years show up for X1 (that would be the starting year +5) and 60 data points (cells in Range) for Y1 and Y2.

Make sense?

B.
Martin LissOlder than dirtCommented:
Yes that does but for the Y axes will there always be just two, resulting, in this case, with Y1 Axis being G9:G38 (30 cells), and  Y2 Axis being H9:H38 (30 cells), or will there be 10 Y axes each 6 cells wide, in other words Y1 G9:G14,  Y2 H9:H14, Y3 I9:I14, etc.?
Bright01Author Commented:
No.  Only a Y1, and potentially a Y2 with a string of data cells to the right that adjust to the changes in the Frequency Metric and total Interval.  

That's because the data that is going to be manually put in is represented as Units Sold (X1) and Units Returned or Failed (Y2) such in the case of determining a gap in "Warranty Analytics" between two dates (which is represented on the X1 Axis.

Does that help?
Martin LissOlder than dirtCommented:
Still unsure about what you want. Please show a picture of what the table should look like for the conditions stated in post 40952041.

I also think you have a problem with the current code. If the # of intervals is 2 and the metric is Week and the Frequency is Month then the # of data points is .46. I don't think you should allow the Frequency to be a longer time period than the Interval.
Bright01Author Commented:
Hopefully this will be a better representation.

B.
D--Data-Data-Temp-Visual-of-Macro-R.pptx
D--Data-Data-Temp-Macro-to-create-D.xlsm
Martin LissOlder than dirtCommented:
Sorry still not understanding. Here's a picture of a table with 24 headers from March 2012 to March 2014. Do you really want 96 cells in Range Y1??? If so what's the purpose of the headings since after March 2014 there aren't any headings?
???
Bright01Author Commented:
Great question.  So this is strictly a Data Table.  So what happens is that there are 96 data readings or points of input that happen in the Interval and that's why there are 96 cells laid out for Y1 and Y2.  there is no heading info.  When it comes to using them in a graphic, I'm planning to reference the range for X and the ranges for Y1 and sometimes Y2.

If we changed it to 4 and Years with the frequency being months, then the data table for (Y1 and Y2) would be 48 periods.  The data table would be blank for Y1 and Y2 until they were manually populated.

Does that help?

B.
Martin LissOlder than dirtCommented:
OK we're getting somewhere now. In your last post you say "there is no heading info". How does that reconcile with the table layout in your workbook where you show "2015" in G8, "2016" = H8, etc.?
Bright01Author Commented:
That is the X1 Axis information (i.e. the Interval).  In the example I sent (WB), I have 4 years selected.  In G8 is the Start Year, H8, 2016, I8, 2017, J8, 2018 and K8, 2019 (or the End Year).  That's not heading info.  It's Date/Time Points associated with the Interval.  Within the Interval, there are 48 data points for Y1 and 48 for Y2 that represent the data values that will be collected over that period of time (the Interval).

Helpful?

B.
Martin LissOlder than dirtCommented:
In my picture, E1 contains "Jul-12". That may not be a heading but I assume you want it there. If so and the user were to put something in E2, it would look to me like that was associated with July 2012. Is that correct?
Bright01Author Commented:
No.  So for example; The only relationship is that if the Interval is "Years" and the Frequency is "Months", the first 12 data points in the Y1 and Y2 Rows will be associated with the first Interval Value....and the next 12 data points will be associated with the second interval value....etc.; but that relationship won't show up until it is graphed on their respective axis's.  So in the Table, it's simply 1 stream of Date/Time Elements, and 3 Ranges, 2 of which are not filled in until the user enters the data points.  It would be nice if as the Data Points in the Range are created, that the cells show a boarder.  That way you can see it expand or contract based on the number of Frequency points resulting from the selection.

Make sense?

B.
Martin LissOlder than dirtCommented:
OK here's my assumption now.

You don't really want/need a table. You just want an area with 3 dynamically defined named ranges. X1_Range (note that there can't be spaces in the name) would start in G8 and extend to the right for however many '# of Intervals' there were. The cells in X1_Range would be filled by the macro.
Y1_Range would start in G9 and extend to the right for however many '# of Intervals' there were.
Y2_Range would start in G10 and extend to the right for however many '# of Intervals' there were.
Cells in the 3 ranges should have borders.
Bright01Author Commented:
Yes.  I'm sorry I referred to this as a table.  I think you have it right.  I will then take those ranges and figure out (may need some EE Help) in how to get them into a graphic using the range names.  The end result should be a graph that changes as the ranges change based on the selection of Interval and frequency.  I knew this one was going to be tough ... but you asked all the right questions.

Also, you are correct.  The Frequency cannot be greater then the selected Interval.

B.
Martin LissOlder than dirtCommented:
Please take a look at the Interval_frequency sheet in the attached workbook. It shows the results of running the new CreateRanges macro with the current values in C8:C11. It will take a bunch more work to make it flexible but I don't want to go any further until I'm sure I'm on the right track. So please tell me what problems you see.
28709098.xlsm
Bright01Author Commented:
Martin,

I looked over the Interval_Frequency sheet and you fixed the selection process.  When I changed the Interval to Weeks and changed the number to 4, and the Frequency to Days (result 28), the previous formula producing new date and number of Frequency events show the right number.  I tried to run the Create the "Create Table" macro but got an error.  The only other difference is that I'd prefer to have the "Table" (X1, Y1, Y2) listing/range, on the same tab if possible.  If I'm doing something wrong in running the macro or can bring more clarity to the request, just let me know.

B.
Martin LissOlder than dirtCommented:
As I said "It will take a bunch more work to make it flexible" so the workbook above only works for Year.

Please explain what you mean when you say
The only other difference is that I'd prefer to have the "Table" (X1, Y1, Y2) listing/range, on the same tab if possible.
Aren't they all on the Interval_Frequency sheet?

In any case here's an updated workbook. It arbitrarily limits the number of data points to 200. To change that, change this line. Const MAX_POINTS = 200 You can also change the two constants above it to change the position of the ranges on the sheet.
28709098a.xlsm
Bright01Author Commented:
Got it.   Just tried it.  The Y1/Y2 gets generated correctly and the X1 (Date/Time) also creates the right number of cells.  However, the dates changed to 1905 for some reason when you change the Interval.  (e.g. from 4 to 1 or 2).

It looks like it is all on one Tab.  What through me off was you have another WS that has data in it.  I thought you might be using that to create the ranges.... but it may just be scratch.

B.
Martin LissOlder than dirtCommented:
What should the dates look like in the X1 range? I assume it depends at least in part on what the Interval metric is.
Bright01Author Commented:
So the X1 Data set is strictly the Date/Time selection in the Interval # and Interval Metric.   So if you select 4 as the Interval # and "Year", you would produce 4 years tacked onto the Start/Date.  If you pick 4 as the Interval # and "Month", you would produce 4 months tacked onto the Start/Date.   The only relationship to the Frequency is that the data points occur within the total Interval.

Does that help?

B.
Martin LissOlder than dirtCommented:
Does the way the Start Date/Time is displayed have anything to do with how the values in X1 are displayed?

Tell me if this is correct and replace the "???"
If the start date/time contains 2105 and the Interval Metric is Year I should put 2015, 2016, etc in X1.
If the start date/time contains August and the Interval Metric is Month I should put Aug, Sep, etc in X1.
If the start date/time contains August 29 and the Interval Metric is Week I should put ???, etc in X1.
If the start date/time contains August 29  and the Interval Metric is Day I should put ???, etc in X1.
If the start date/time contains 09:30 and the Interval Metric is Hour I should put ???, etc in X1.
If the start date/time contains 09:30 and the Interval Metric is Minute I should put ???, etc in X1.
If the start date/time contains 09:30:21 and the Interval Metric is Second I should put ???, etc in X1.
Bright01Author Commented:
You are correct!  If you change the Sub ChangeFormat, the X1 should also reflect that.  This gives us the ability to format the X1 Range. Right?

B.
Martin LissOlder than dirtCommented:
I'm sorry but I need more information. Please fill in the rest of the cells in the attached workbook. They represent what the first cell in the X1 range should look like,
Grid.xlsx
Bright01Author Commented:
Martin,

You'll be pleased to know I had to give this a lot of thought since you pointed out that depending on the selection of the Interval and Frequency, the X1 Data Set could get very complicated.  After reviewing options, I think it best to have a single data format (for the grid, leaving the original macro to calculate the frequency points just as is with 1 exception.... adding seconds.) for generating X1.  I have attached the information you requested on the grid.  You will see that if we add "seconds" to the Start Input, we can then calculate any/all Intervals.  I think it best to keep the calculation simple and I believe we can format the calculation later if desired.

Agree?

B.
Copy-of-Gridv2.xlsx
Martin LissOlder than dirtCommented:
Here's an update.

In this workbook I corrected problems with the workbook structure so you no longer have a "wbkQ_28703272" item under “Microsoft Excel Objects” and I renamed "basQ_28703272" to "Module".

We've talked about some small changes that I think should still be made. One of them is that you want to produce data entry ranges Y1 and Y2  that match the # of Data Points but your formula for determining that can produce fractional results. A range of course can not contain a fractional number of cells so for example with

intervals = 4
metric = Month
frequency = week

you get 17.42 data points but 17 cells in Y1 and Y2, so one cell that would contain that mythical .42 data points is "missing"

but with

intervals = 5
metric = Month
frequency = week
==> you get 21.77 data points but 22 cells in Y1 and Y2 so the last cell is where the mythical .77 data points would be placed.

I think it would be better if the formula results were adjusted so that it would always either round up or round down. I think rounding down makes the most sense but let me know if you want to change it and if so how.
28709098b.xlsm
Bright01Author Commented:
Martin,

This looks very good!  I agree, we should simply round up or down.   This does not need to be 5/9 precision.  

One more (hopefully small) suggestion.  Now that we have the date/time down to a single data point (the newly created X1 data cells, can we extend the formatting so that you could select (the same way you do now with the blue button) that same format, on the X1 Data Points?  

B.
Martin LissOlder than dirtCommented:
we should simply round up or down
Which one?
Bright01Author Commented:
Interesting problem.  In most circumstances, I'd say we will be more safe always rounding down.  Data will always exist (albeit we would miss the final data points), if the fraction isn't included.  BUT, if we have a large number of frequency points, say Month, 2 and Frequency in Sec. we could miss a significant number of data points.  Can we give the option?

B.
Martin LissOlder than dirtCommented:
I will round down because the fraction of a datapoint will always be less than a datapoint. For example if interval was Seconds and the datapoints were 123456.78, the .78 would be .78 seconds.
Bright01Author Commented:
Yes...agree that most of the time, rounding down would be better.  However what if the Interval was Years, a fraction of a year could be a significant number of lost data points if the frequency is in seconds.  I'll live with it.

b.
Martin LissOlder than dirtCommented:
It's possible I'm confused but wouldn't the .95 here be .95 seconds?
????
Bright01Author Commented:
Sorry.... you are correct.  My bad.

Round down.

B.
Martin LissOlder than dirtCommented:
When you say
Now that we have the date/time down to a single data point (the newly created X1 data cells, can we extend the formatting so that you could select (the same way you do now with the blue button) that same format, on the X1 Data Points?
Are you saying that you want the X1 cells to look like (the same format as) C8?
Do you want X1 to change if the blue button is clicked, even if nothing else is done?
Or do you want a second blue button just for X1?
Martin LissOlder than dirtCommented:
Please don't miss my questions in the previous post but I also want to point out what I was trying to get at when I posted grid.xlsx in post ID: 40953430, and that was that as far as the X1 range goes, some of the format choices don't make sense as shown below so if you agree then please tell me how they should be displayed.
123
Bright01Author Commented:
Martin.  You really are a Saint to put up with this much back and forth.  But all your points are spot on.

So what should happen in an "ideal" world, is that the formatting for the X1 Data Stream should match the formatting in the selection of Interval metric.  The Start Date will always be in Date/Time... simply because that's the most precise metric  Month/Day/Year/Hour/Min/Sec. to declare and covers all elements except for Weeks.  Once the Start Date is declared, then  the Frequency is selected (now = to or < the Interval) and Y1/Y2 are declared as a range that has blanks to be filled in.  If the Interval is selected as Years, the X1 data should also be in Years.  Months?  Months. Days? Days. etc.

What we had discussed in an earlier post was to try to keep it simple so the Macro could make the calculations without worrying about formatting and that we would do the formatting in the X1 cells separately.  I had thought that if we tied it into the original macro (fired by the blue button) we could simply select the same format in X1.  That may be overcomplicating things.  

How about we do this..... let's simply create X1 with the Date/Time (rounded) result.  We should close this question out.  Then I will then test it and author another question concerning formatting.

Will that work as a plan?

B.
Martin LissOlder than dirtCommented:
OK but what do you mean by "Date/Time (rounded) result"? Do you mean for example 8/26/2015  9:00?

And could you explain why you need all the different date and.or time formats?
Martin LissOlder than dirtCommented:
See my post above. This formats X1 the way I think you mean and it also rounds down the number of data points. It also contains a number of other small improvements. Are we doing anything about the "Revised Time Relationship Array"?
28709098c.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
A+!  Martin, thank you very much.  I'll test this but you did a great job in making sure we nailed the requirements.  This was a fun project to work with you on.  Outstanding Project Mgmt. on your part.  Will test and post another question shortly.

What did you mean, Are we doing anything about the "Revised Time Relationship Array"?   I have no other questions outstanding............

B.
Martin LissOlder than dirtCommented:
You're welcome.

I was talking about the  "Revised Time Relationship Array" at the bottom of the sheet. Both the headers and the left column are referenced in code but the reference is hard-coded so if a user were to add or delete a row then those ranges would change and the code wouldn't work properly. I suggested that
1. The headers and/or the left column be should be changed to Named ranges, or
2. The grid should be moved to another, protected and/or hidden sheet, or
3. The structure of the current sheet should be protected so as to prevent addition or deletion of rows.

#1 is the easiest thing to do but that wouldn't prevent someone from deleting rows in the grid.
Bright01Author Commented:
Martin...... I have not done as you said yet given the complexity.  I am however, posting a new request to move the macro you have written, into the graphic that Taylyn wrote for me.  I probably need to get the graphic working first then see if you are available for hire to get this cleaned up.  Does that make sense?

Thanks again,

B.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.