?
Solved

excel question - is it possible?

Posted on 2014-08-04
8
Medium Priority
?
109 Views
Last Modified: 2014-08-05
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
0
Comment
Question by:Jagwarman
  • 4
  • 4
8 Comments
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40238734
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
 

Author Comment

by:Jagwarman
ID: 40239375
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
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40239407
=OFFSET(Sheet1!$G$1,MATCH("Grand Total",Sheet1!$G:$G,0)-1,0,1,14)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Jagwarman
ID: 40240854
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
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40240872
Can you upload your file and I will have a look?
0
 

Author Comment

by:Jagwarman
ID: 40240897
Thanks Rob.
GrandTotal.xlsx
0
 
LVL 34

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 40240917
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
 

Author Comment

by:Jagwarman
ID: 40240932
thanks thats great
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

840 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