hedgeselect

asked on

# Dynamically updating ranges in Excel - please update my spreadsheet

Could someone who is good at working with dynamic cell referencing, please make changes to the attached spreadsheet so that as new monthly performance return data is added to column B on worksheet 1 and columns B, E and H on worksheet 2, the following is dynamically updated:

In the summary statistics table on worksheet 3:

1. Each of the 4 lines in the chart are dynamically updated so that I don't need to do anything when a new monthly return is added to worksheets 1 and 2

2. Cells C21 to C24 and C26 to 27 need to also dynamically update with the new additional monthly returns

Thanks.

Master-Statistics-template-1.xls

In the summary statistics table on worksheet 3:

1. Each of the 4 lines in the chart are dynamically updated so that I don't need to do anything when a new monthly return is added to worksheets 1 and 2

2. Cells C21 to C24 and C26 to 27 need to also dynamically update with the new additional monthly returns

Thanks.

Master-Statistics-template-1.xls

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi

hmmmm..not sure what happened but:

1. C26 on the stats table still not updating the negative months but total months is increasing correctly

2. C27 on the stats table is giving a wrong figure - it should be taking the highest monthly loss i.e. on col B first worksheet

3. Chart not correctly updating with monthly dates when I dump more monthly fund data

....nearly there though. I'll check again in the morning, off to sleep now. hard work verifying this :)

Cheers.

hmmmm..not sure what happened but:

1. C26 on the stats table still not updating the negative months but total months is increasing correctly

2. C27 on the stats table is giving a wrong figure - it should be taking the highest monthly loss i.e. on col B first worksheet

3. Chart not correctly updating with monthly dates when I dump more monthly fund data

....nearly there though. I'll check again in the morning, off to sleep now. hard work verifying this :)

Cheers.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi

Ok I think we're almost done now. Just on point 3, I have added more data to both the fund and the three benchmarks and whilst the date range does indeed update, the Fund line does not, it's stuck Feb 2011?

As more data is added to the tables and the date increases, what do you recommend should be done about the date range on the chart as it will get more "cluttered". Is there a way of saying, "after x number of monthly data points, goto semi annual reporting instead of quarterly and if there is less than 12 then keep it as quarterly"?

Master-Statistics-template-1-v2.xls

Ok I think we're almost done now. Just on point 3, I have added more data to both the fund and the three benchmarks and whilst the date range does indeed update, the Fund line does not, it's stuck Feb 2011?

As more data is added to the tables and the date increases, what do you recommend should be done about the date range on the chart as it will get more "cluttered". Is there a way of saying, "after x number of monthly data points, goto semi annual reporting instead of quarterly and if there is less than 12 then keep it as quarterly"?

Master-Statistics-template-1-v2.xls

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

On the event _Activate of Sheet3 - where your graph is, I added this code:

Which sets the x-axis major unit to 1 for monthly if months <=12, and to 3 for quarterly if > 12 but <=24, and then for 2 to semi-annual otherwise.

Pls see attached.

Dave

Master-Statistics-template-1-v2-.xls

```
Sub SetXAxis()
Dim i As Long
i = [FUND_1].Rows.Count
Sheet3.ChartObjects("Chart 5").Select
With ActiveChart.Axes(xlCategory)
If i <= 12 Then
.MajorUnit = 1 'do monthly
ElseIf i <= 24 Then
.MajorUnit = 3 'do quarterly
Else
.MajorUnit = 6 'do semiannually
End If
End With
End Sub
```

Which sets the x-axis major unit to 1 for monthly if months <=12, and to 3 for quarterly if > 12 but <=24, and then for 2 to semi-annual otherwise.

Pls see attached.

Dave

Master-Statistics-template-1-v2-.xls

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi Dave,

Thanks again for your efforts.

To be fair to you and to help you move up the rankings, I'm going to close this question and award you the points, but would like to open up another (and possibly more) questions to make further modifications to this excel chart along the same sort of lines of what you are capable of doing.

My preference would be for you to do it since a) you're fast and b) you're good and c) you know what I want. So consider this question closed, but what I now want is:

1. Two new columns in worksheet 1 (Fund Data worksheet), perhaps between existing columns E and F which look at a) Year To Date (YTD) returns and b) the other column to have 12-month rolling returns (both based on the monthly fund data).

Just to be clear, the YTD returns will always show the cumulative return in that year, so for 2011, it will be Jan 11 and Feb 11. For complete years the YTD will match the Yearly returns. The rolling returns will fill every cell in the new column and also look at the cumulative returns for exactly a 12-month period regardless of what the current month is.

If you are happy to work on this, I thought maybe if you want to work on it here and when done, post the spreadsheet here and I can open a new question and you can just post the spreadsheet there too so it makes it easier to award you the points?

As with the rolling 12-month chart from the other question, are you able to include that chart in this spreadsheet here such that it is a second chart in the third worksheet? I'll obviously close that question off and this one at the same time and award the points.

Thx again.

Thanks again for your efforts.

To be fair to you and to help you move up the rankings, I'm going to close this question and award you the points, but would like to open up another (and possibly more) questions to make further modifications to this excel chart along the same sort of lines of what you are capable of doing.

My preference would be for you to do it since a) you're fast and b) you're good and c) you know what I want. So consider this question closed, but what I now want is:

1. Two new columns in worksheet 1 (Fund Data worksheet), perhaps between existing columns E and F which look at a) Year To Date (YTD) returns and b) the other column to have 12-month rolling returns (both based on the monthly fund data).

Just to be clear, the YTD returns will always show the cumulative return in that year, so for 2011, it will be Jan 11 and Feb 11. For complete years the YTD will match the Yearly returns. The rolling returns will fill every cell in the new column and also look at the cumulative returns for exactly a 12-month period regardless of what the current month is.

If you are happy to work on this, I thought maybe if you want to work on it here and when done, post the spreadsheet here and I can open a new question and you can just post the spreadsheet there too so it makes it easier to award you the points?

As with the rolling 12-month chart from the other question, are you able to include that chart in this spreadsheet here such that it is a second chart in the third worksheet? I'll obviously close that question off and this one at the same time and award the points.

Thx again.

ASKER

Oh, and why do I get those green little arrows in the cells on worksheet 1 i.e. cell errors. How to get rid of those and still have the formulas copy up and down? I need to make sure that the empty cells at the top of the B to F will work correctly if I ever dump data in there for funds that have longer track records i.e. more data points prior to Feb 08.

Thx.

Thx.

ok - just refer to this question when you create the new question so I'll see it. I can add the 12 month option to this one...

Cheers,

Dave

Cheers,

Dave

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi Dave,

Please continue this conversation on a new question, posted (to be clear) as I have asked further questions on this topic. I will now close this q.

https://www.experts-exchange.com/questions/26870302/Dynamically-updating-ranges-in-Excel-dlmille-please-update-my-spreadsheet-1.html

Thanks.

Please continue this conversation on a new question, posted (to be clear) as I have asked further questions on this topic. I will now close this q.

https://www.experts-exchange.com/questions/26870302/Dynamically-updating-ranges-in-Excel-dlmille-please-update-my-spreadsheet-1.html

Thanks.

ASKER

Top marks for Dave, excellent solution!

ASKER

Thank you so much :)...and you have gone beyond the call of duty with the extra bits, which actually I had in mind but didnt ask, so thanks again.

I'll be honest and say that I honestly dont know what all that code means or how it works, and to be honest, I dont care as long as it works (though I would love to try and understand it when I get the time).

1. Could I please ask you to check cell C26 on the stats table as it does not appear to update the Negative Months:Total Months when I throw in more monthly numbers?

2. Also I see that the new chart you created, the x axis is based on number of months. For comparison purposes, is how easy and quick would it be to create a replica of my existing chart with the same title axis etc, colour, etc as I need to stick to my format?

Truly awsome again - how long did this take you to do?

If you feel up to it, please feel free to use your final version template after the last update above, to reflect rolling returns if you wish to participate on https://www.experts-exchange.com/questions/26839152/Dynamically-updating-charts-in-Excel.html

I will shortly close this question off and award you the points.

Top man!