We help IT Professionals succeed at work.

# excel question - is it possible?

on
125 Views
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?

Comment
Watch Question

## View Solution Only

Finance 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

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

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

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

Commented:

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)

Commented:
thanks thats great
Unlock the solution to this question.

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.