Link to home
Start Free TrialLog in
Avatar of Bright01
Bright01Flag for United States of America

asked on

Dynamic Array Creation

I am working on a WS Application that two EE Pros. helped me out with. I am now looking to add the next level to the model.

I need a dynamic array (i.e. Financial_Calc Tab) that is created as changes occur to a set of range names and input data that is inputed on another Tab (i.e. Financials).  

The current macros are clean and well written and automatically add input data columns either before or after the current year and also reset the model.  As those columns are added, it automatically adds or removes unique range names.  I need a macro that automatically detects the changes and appropriately adds or erases the cells in an array (thus making it dynamic).  

That's it for now!

Thank you in Advance,

Bright01
Copy-of-Financial-Model-v4.xlsm
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

I am having difficulty understanding the requirement.  I can see how the 4 buttons on "Financials" work. Can you further spell out what needs to be done when these buttons are clicked.... an example?
Avatar of Bright01

ASKER

OK...so when a new column is created with the existing macros, data will next be entered into the fields (in yellow).  What I need is on the Financial_Calc Tab is for a dynamic range array be created so that as data is entered in the first tab, it fills out the array in the second tab. I plan to then do mathmatical calculations on the second tab array, on the data that is inputed.  When a column on the first tab is deleted, the array needs to adjust to the changes.  When it's reset, the array is also reset (thus being dynamic).

Does that make sense?

B.
The term "dynamic array" is usually understood to refer to a range of cells that grows or shrinks with the number of entries in it. If I understand you correctly, you would like to enter data in the 'Financials' worksheet and somehow get them reflected in the 'Financials_Calc' sheet. This part seems, indeed, in need of a little more explanation. An example would be decidedly helpful.
Faustulus,

I did include a update on the WB (see above).  

What I don't know how to do is to create a array (on the second tab), from the named ranges that are created in the first tab as columns are added or reset.  Then, do you define a named range with the new column on the new tab or does the table on the new tab change based on new columns or data on the firtst tab?  

When you add a year (a new column) on the first tab, does it automatically (dynamically) change the array on the second tab?  This is what I don't know how to do.

Make sense?  

B.
Do you want to have range names on the calc tab in the same way they are on the financial tab?
Great question!  I think the easiest and cleanest way is to have them exactly the same.  On the otherhand, are there advantages in an array where having all the data compacted work better for manipulation purposes?  Perhaps there is a way to do both with having the slight change in code commented out unless I need to use it later.  Otherwise, stick to what is easiest and most "eligant".

Thanks for asking,

B.
You may be too modest. Excel is absolutely great at displaying anything anywhere. Usually this is done on a cell by cell basis. If you would tell us that you would want any change in Financials!H10 (Net sales) automatically reflected in Financials_Calc!H10 we would all know what to do. You could also refer to Financials!H10 as "Net Sales 2013" with the same effect. What I can't get a handle on is the big red square in your second sheet, reaching over 6 columns and no cell associated with anything - it would appear.
The big red square can be deleted.  It was only there to reflect the array.  My confusion is not with desplaying whats on the first tab on the second tab.....it's with making it dynamic.

Make sense?

B.
Since we don't seem to mean the same thing when we say "dynamic" perhaps we should seek for other words. Same probably applies to "array".
On Tab1 you have a set of data for the current year. Do you wish to display the same data on Tab2? If so, in which cells?
There are 14 rows of financial data on Tap1. 11 of these are used for numbers, the others being blank. I see that you also have 14 rows in your red square on Tab2. Is th number matched intentionally?
Why are there no row descriptions on Tab2? Do you intend to import them from Tab1?
Faustulus,

Answers:

1.) The array I'm building is for calculation purposes....not display, so probably having only those fields that contain data input in a row/column format would be best.  It won't matter which cells as long as the array is contiguous (compressed).

2.) The red square was only a prop. to represent the array....so no the numbers were not intentionally made to match.

3.) It would be helpful to have the row descriptions also present so as to be easier to do math on the data and know which rows are represented.

Make sense?


B.
In addition to the answers above, perhaps a little more explanation is in order.  

The second tab is for driving a set of very advanced calculations that will not be exposed to the user (e.g. average profit/yr., trend anaysis within row category, etc. etc.  The idea behind making it dynamic (i.e. able to expand and contract with changes to Tab 1 is to give the user the ability to add, delete, reset the model without any consideration to the array and while in future questions you will see, the data in the array will be summed and math will be performed on whatever is in the array.

Make sense?

B.
Can you please create a mock-up of Tab2 as you visualise it.
Here is my doubt:-
You have all the numbers on Tab1. On Tab2 you seem to want some kind of copy of Tab1 numbers, just "compressed" as you say. Then you wish to use those numbers to make some complicated calculations.
Why can't you use the numbers on Tab1 to do those calculations? While Tab2 is constantly being updated with the most recent numbers from Tab1, how can the two sets of numbers ever be different from each other?
Faustulus,

Just did..... attached.  It's a mock-up of what I'm trying to accomplish by building a very flexible input and array creation capability.  The problem is, if it is not dynamic, then I have to plan for how many years of data we are collecting and it differs every time we create a case.

Make sense?

B.
Copy-of-Financial-Model-v5.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Faustulus
Faustulus
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Faustulus,

Great job!  If I follow this correctly; I can now add previous years, put in the data and it will automatically populate the Array on Tab 2.  That's exactly what I was trying to do.

Next I will be posting a question about how do you set up formulas (in Tab 2) that only count when the contiguous fields are populated.  Hope you will stick with this.....it's going to be a great littleWS.

Thank you again.

B.