Excel formula

Posted on 2014-09-09
Last Modified: 2014-09-12

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?
Question by:hellblazeruk
  • 2
  • 2
  • 2
  • +1

Expert Comment

ID: 40312784
Are you using a pivot table for creating the chart?
LVL 27

Expert Comment

by:Glenn Ray
ID: 40313233
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.

LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 100 total points
ID: 40313280
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.

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

LVL 50

Accepted Solution

Ingeborg Hawighorst earned 400 total points
ID: 40313576
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.

Author Comment

ID: 40314899
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
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40315760
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


='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:


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

Author Closing Comment

ID: 40318862
Excellent solution and a new way of working, thank you very much.

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question