excel question - is it possible?

Jagwarman
Jagwarman used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
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

Author

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
Rob HensonFinance Analyst

Commented:
=OFFSET(Sheet1!$G$1,MATCH("Grand Total",Sheet1!$G:$G,0)-1,0,1,14)
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

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
Rob HensonFinance Analyst

Commented:
Can you upload your file and I will have a look?

Author

Commented:
Thanks Rob.
GrandTotal.xlsx
Finance Analyst
Commented:
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

Author

Commented:
thanks thats great

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial