Trying to Trend Dynamically from Model Data

**EartheDice** · 03-09-2018, 03:50 PM

Hey all -- New to the forum as I came here in search of answers I have yet to find regarding the title of this thread. This seems like basic stuff (I am about at intermediate level btw), but I have yet to come up with a solution. I have a lot of processes where I either dynamically populate models from pivot tables or formulated / aggregated table data. I then would like to dynamically populate the results from those cells into another table for trending purposes.

I have yet to figure out how to do this dynamically each time I run a new report, say monthly. Let me try to walk through the scenario:
1) Cell is populated in model from another worksheet formula or cell.
2) On another worksheet, I have a table set up to populate from the model cell each time I run the monthly report that feeds a pivot table. Obviously, this is going to change the previous cells in the trending table to the current data unless I go in first and change the previous month's formulated cells to text values by copying and pasting. The other horrible method I have been using is to keep another column to the right of the formulated data and paste the values each time there so I can do averages on the trending data monthly etc. and paste back into the previous month's cell.

This is a terrible method but I have yet to be able to solve it or find any answers. My goal is to set up the trending table with months in the left column and the data in the right column that gets populated each month via a formula linking to that cell. I am assuming there has to be a way to do this, but I have looked through a ton of formulas and none of them seem to make a ton of sense. My formula skills are probably my weakest skill in excel fyi.

Any help would be appreciated as this is very difficult to search for given the description pulls up anything but my question.
Thanks

Edit: I have created a simple example of what I am trying to accomplish. Thanks for any help you can provide.

**kroko** · 03-10-2018, 12:24 AM

By attach as sample file will help us to get it understand your questions/requirements quicker.

**protonLeah** · 03-10-2018, 01:09 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

**EartheDice** · 03-10-2018, 01:14 AM

Changed the title.

**EartheDice** · 03-12-2018, 09:41 AM

I have added an attachment to the original post providing a very basic depiction of what I am trying to accomplish.

**JeteMc** · 03-15-2018, 01:47 PM

Hello EarthDice and Welcome to Excel Forum.
Thank You for changing your title.
In reading the description from post #1 and looking at the attached file, it appears that the value in Sheet1 B3 and Sheet2 C3 both reference Sheet3 B11, and that Sheet2 C3 doesn't necessarily need to reference Sheet1 B3. If that is the case then perhaps the following will help.
1) The columns of data from which the sums are derived are amended to the right*.
2) The formula to get each successive sum is:

Formula:

Please Login or Register  to view this content.

*As there are 16384 column (2010 version) this could be done for 1365 years. Admittedly I don't know the mechanism by which the columns of data are placed and that will have an impact on the practicality of this suggestion.
Let us know if you have any questions.

**EartheDice** · 03-15-2018, 02:44 PM

Thank you JeteMc. I think we are on the right track but I am afraid my first example worksheet was not a great example. I will be using the same table to derive sum each time I run the report with repopulated data. There will be several of these tables in the worksheet doing similar and populating the model but I am only providing one example. I bring that up because a macro is not a good solution for what I am needing to do with many of my reports so I need a solution or formula that can populate my trending chart dynamically without changing the previous months' results in the trending table. I think my attached example will help clarify what I am trying to say. I realize the trending table can be fed data straight from the source and not the model itself but that really isn't the point for what I am trying to accomplish....if it is even possible.

**JeteMc** · 03-19-2018, 09:09 PM

Seems as if you want to replace the values in B2:B10 with new values, then put the new sum in the next blank cell in column C as in what is on your after sheet, while leaving the previous sums in column C. The only way that I have heard of to do that is using VBA, of which I am illiterate. My suggestion would be to ask one of the moderators (i.e. AliGW) or administrators (i.e. FDibbins) to move the thread to the Excel Programing/ VBA/ Macros forum.