Solved

Excel formula

Posted on 2014-09-09
7
172 Views
Last Modified: 2014-09-12
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?
0
Comment
Question by:hellblazeruk
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 7

Expert Comment

by:tomfarrar
ID: 40312784
Are you using a pivot table for creating the chart?
0
 
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.

-Glenn
0
 
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.

-Glenn
EE-DynamicChartPlot.xlsx
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 50

Accepted Solution

by:
teylyn 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.
OneChartPlot.xlsx
0
 

Author Comment

by:hellblazeruk
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
=OneChartPlot.xlsx!chtSeries
0
 
LVL 50

Expert Comment

by:teylyn
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

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
 

Author Closing Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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.

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now