Dynamic Legend Key?!

[BigDelGooner]

I have a chart where I would like one of the legend keys to read:

"Current Weight as at..."

and then whichever date I have selected from the valiation list in cell B3.

I thought, within the source data menu, I could just change the series name to something like ="Current Weight as at "&TEXT(B3,"DD/MM/YY") but this doesnt appear to work.

Can anyone help? Any ideas?

 

[Andrew Poulsom]

Put that formula in another cell and reference that cell for the legend.

 

[BigDelGooner]

Thank you so much for help Andrew, always such a simple answer. Shame you can't just put that straight into the field though.

 

[BigDelGooner]

Actually this has presented another question for me, how do I change the reference for the values based on the date selected in cell B3.

The reference for the values currently looks like this:

=Data!$B$30:$T$30

However this will not change dynamically when a different date is selected in cell B3. I tried a similar method to the legend key as you just told me but it doesnt work. I basically entered the following formula directely into a cell and tried to reference that..

="Data!$B$"&MATCH(B3,Data!A:A)&":$T$"&MATCH(B3,Data!A:A)

 

[Andrew Poulsom]

Choose Insert|Name|Define, in the Name box enter Values,in the Refers to box enter:

=INDEX(Data!$B:$T,MATCH(Sheet1!$B$3,Data!$A:$A,FALSE),0)

and click Add, Then OK. The formula assumes that B3 is on Sheet1 - adjust the sheet reference if necessary.

Now set the source for the chart's values to =Data!Values.

 

[BigDelGooner]

Once again I can't thank you enough Andrew, your help is very much appreciated.

 

[BigDelGooner]

Last question Andrew I promise...

I have a chart that has the following data range:

=Data!$A$2:$T$2,Data!$A$5:$T$5,Data!$A$10:$T$30

Each month I will drag down the data set one more row and therefore the data range will have to change as follows:

=Data!$A$2:$T$2,Data!$A$5:$T$5,Data!$A$10:$T$31
=Data!$A$2:$T$2,Data!$A$5:$T$5,Data!$A$10:$T$32
=Data!$A$2:$T$2,Data!$A$5:$T$5,Data!$A$10:$T$33
etc.

Is there anyway, without using VBA, to do this? If I enter a data range that is larger than the current data table (i.e. 30 lines) its leaves gaps in the chart.

 

[Andrew Poulsom]

Can't you hide the unwanted rows? They won't be plotted if 'Plot visible cells only' is checked under Tools|Options|Chart tab.

 

[BigDelGooner]

Thanks Andrew.

I was trying to avoid that because I didnt want users to have to unhide rows to enter the next month's line of data.

However I have just written a tiny bit of code to unhide the rows on the 'data' tab when it is activated and then vice versa when deactivating.

All seems to work ok so thanks once again.

Kind regards
Stuart