# 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?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Are you using a pivot table for creating the chart?
0
Excel 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
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
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author 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
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
Author Commented:
Excellent solution and a new way of working, thank you very much.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.