excel question - is it possible?

I have only ever worked with graphs where the data is static. i.e. I have 13 columns [Jan 13 -Jan 14] and 20 Rows of data..

I have now been ased to create a graph where I will have 13 columns but the number of rows of data will change.

I therefore need to be able to ensure that I always pick up the 'Grand Total' row which  will be 'Legen Entries (Series)' The Horizontal (Category) Axis [Columns] will be static

Is this possible?

Thanks in advance
JagwarmanAsked:
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.

Rob HensonFinance AnalystCommented:
Possible - Yes.

Easy - Fairly.

Use a dynamic named range to set the range for the Grand Total row.

use the following to create the range in the Name Manager:

=OFFSET(Sheet5!$A$1,MATCH("Grand Total",Sheet5!$A:$A,0)-1,0,1,14)

Assuming Named Range of "GRAND_TOTAL"

For the graph then define the data range using:

=Sheet5!GRAND_TOTAL

Adjust all references to Sheet5 to the relevant sheet name and reference to column A in formula to correct column in data.

Thanks
Rob H
0
JagwarmanAuthor Commented:
I know I am being anal and should be able to work this out but I can't seem to get my head around it.

My Graph is in Row G4 through U4

So Grand Total is in G

I presumed I changed yours to

=OFFSET(Sheet1!$G$1,MATCH("Grand Total",Sheet1!$A:$A,0)-1,0,1,14) But it does not appear to work for me.

Thanks
0
Rob HensonFinance AnalystCommented:
=OFFSET(Sheet1!$G$1,MATCH("Grand Total",Sheet1!$G:$G,0)-1,0,1,14)
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

JagwarmanAuthor Commented:
Rob either I am being really stupid or my PC is playing up. My money is on me.

you say "Use a dynamic named range to set the range for the Grand Total row" so I set G;G as Events

you say "use the following to create the range in the Name Manager"
=OFFSET(Sheet1!$G$1,MATCH("Grand Total",Sheet1!$G:$G,0)-1,0,1,14)

So I go into Name Manager and in Name I enter ABC then I enter above formula in the 'Refers To:'

And I get "A formula in this worksheet contains one or more invalid references
Verify that your formulas contain a valid path, workbook, range name,and cell reference.

Sorry to be a burden

Thanks
Regadrs
0
Rob HensonFinance AnalystCommented:
Can you upload your file and I will have a look?
0
JagwarmanAuthor Commented:
Thanks Rob.
GrandTotal.xlsx
0
Rob HensonFinance AnalystCommented:
Hmmm, ok, I am having similar issues!!

Initial look at the file, I thought as its a Pivot Table we should be able to just set up a Pivot Chart to go with the table and have very little in the way of filtering on the cxhart so it just shows the totals. Nice idea - didn't work.

I got round it in a different way. See attached.

I have created a separate table of data pulling the Grand Total from the Pivot Table using the GetPivotData function. The Dates are hard linked to the column headers in the Pivot so will change as they change. The Event row uses the function to pull the TOTAL value for the date specified above. The chart then refers to this smaller data table.

Hope this helps.


Thanks
Rob H
Copy-of-GrandTotal.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
JagwarmanAuthor Commented:
thanks thats great
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.