?
Solved

Excel formula

Posted on 2014-09-09
7
Medium Priority
?
180 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 400 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1600 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
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

765 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