Excel formula

Hi,

I have an excel model where monthly data is entered into a tab called ‘Data Entry’,

rows 4  - 12      For January
rows 17 – 25      For February
rows 30 – 38      For March
rows 95 – 103      For August
rows 108 – 116      For September

From this data I have one tab per month showing relevant charts, each time I need to create a new month I need to edit each of the graphs to look at the correct row on ‘Data Entry’, is it possible to put the month start row in a cell (C3) and month end row (C4) then reference these so I only need to update these cells each month

Current series value for August
='Data Entry'!$M$95:$M$103

For September, the chart tab will be called September,  C3=108 & C4=116, the data will still be on ‘Data Entry’ tab in column M

Is this possible?
hellblazerukAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Whenever I read a sentence that has words like "I have a sheet for each month" I cringe.  Why use a sheet for each month? The data is in one sheet. You only need one sheet and let the user select which month to chart.  That way, you only need to maintain one set of charts, not umpteen.

Building on Glenn's data set, here is one report sheet. Dynamic range names use Offset to cover the selected month.

chtLabel      =OFFSET('Data Entry'!$B$1,MATCH(Sheet1!$B$3,'Data Entry'!$A:$A,0)-1,0,9,1)
chtSeries      =OFFSET(chtLabel,0,11)

Plug the range names into the chart series and the chart label (X Axis). Change the selection and the chart changes.
OneChartPlot.xlsx
0
 
tomfarrarCommented:
Are you using a pivot table for creating the chart?
0
 
Glenn RayExcel VBA DeveloperCommented:
I don't believe it's possible.

Ideally, you'd like to create an INDIRECT reference on your monthly sheet that looks like this:
C3: startrow
C4: endrow
C5: =INDIRECT("'Data Entry'!$M$"&C3&":$M$"&C4)

And then refer to C5 for your chart data.  Unfortunately, Excel won't recognize a formula like this.

The alternative would be to use a dynamic named range that uses OFFSET and MATCH to get the cells.  
rngPlot : =OFFSET('Data Entry'!$A$1,August!$C$3-1,12,August!$C$4-August!$C$3+1,1)

The issue here is that you'd still need to create a new range name for each new sheet, which defeats the purpose.

-Glenn
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
Okay...I'm going to say it's possible, but as you've described it (i.e., manually inserting the start and end rows in C3 and C4), it isn't the easiest way.

If you create a LOCAL range name (i.e., limited to the active sheet) like so (for August):
rngPlot =OFFSET('Data Entry'!$A$1,August!$C$3-1,12,August!$C$4-August!$C$3+1,1)

that range will be automatically copied and updated if you copy the sheet (August) and create a new sheet (September).

However, after updating the start and end rows for the new sheet, you will still need to update the Series Values for the copied chart to refer to the newly-created sheet...
from =August!rngPlot
to =September!rngPlot

You have to reference the sheet name (local) or workbook name (workbook) in these kind of dynamic references.  So, it's still not as automatic as one would like.

FWIW, I've attached an example workbook to show how this might all look.

-Glenn
EE-DynamicChartPlot.xlsx
0
 
hellblazerukAuthor Commented:
Hi teylyn,

Better than I had been looking for, I now need to update my charts,

I am getting stuck defining where it looks for the data,

Under Formulas ->Define Name I have created chtLabel & chtSeries but my current charts are looking at cell ranges (as per below) how would I update these?

series values - current
='Data Entry'!$M$4:$M$12

series values -example
=OneChartPlot.xlsx!chtSeries
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
The first thing you need to do is make sure the dynamic ranges work.  So, set up the dynamic range names. Then change the value in the month drop-down. Open the Name Manager, select the series name and click into the "Refers to" box with the formula. The spreadsheet will then switch to show the data the range name applies to and will encircle it with the "marching ants" border.  Do this for all the dynamic range names. In my example I have a range for the Labels (chtLabels) and one for the Series (chtSeries).

Once you are happy that the dynamic range names are correct for all your months, edit the charts. You will need to change all the series definitions and also the X axis labels.  Click each series and edit it. change

='Data Entry'!$M$4:$M$12

to

='Data Entry'!chtSeries

When you confirm and then edit the box again, you will see that Excel has changed the series formula to use the file name instead of the sheet name:

=OneChartPlot.xlsx!chtSeries

... using the file name of your file, of course

When you use range names in charts, they MUST be preceded by either the sheet name or the file name.  If the scope of the range name is the workbook (which is the default), then Excel will adjust the series formula to include the file name.

Make sure to apply the chtLabels reference to the X axis (Select Data Source  dialog > right hand side "Horizontal (Category) Axis Labels" > Edit button)
0
 
hellblazerukAuthor Commented:
Excellent solution and a new way of working, thank you very much.
0
All Courses

From novice to tech pro — start learning today.