We help IT Professionals succeed at work.

excel question - is it possible?

Jagwarman
Jagwarman asked
on
125 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
Comment
Watch Question

Rob HensonFinance Analyst
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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

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
CERTIFIED EXPERT

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

Author

Commented:
Thanks Rob.
GrandTotal.xlsx
Finance Analyst
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
thanks thats great
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.