Avatar of hedgeselect
hedgeselect
Flag for United Kingdom of Great Britain and Northern Ireland 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
Microsoft ExcelSpreadsheetsMicrosoft Office

Avatar of undefined
Last Comment
hedgeselect

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
dlmille

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
dlmille

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
dlmille

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
hedgeselect

ASKER
ah, this is truely awsome!  I've never been so excited at the thought of my spreadsheets updating like this!  
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!

SOLUTION
dlmille

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
hedgeselect

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.
SOLUTION
dlmille

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
hedgeselect

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
dlmille

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
dlmille

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

 
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

Open in new window


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
dlmille

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
hedgeselect

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.
hedgeselect

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
dlmille

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
SOLUTION
dlmille

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
hedgeselect

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.

hedgeselect

ASKER
Top marks for Dave, excellent solution!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck