# Dynamic Named Range

1. ## Dynamic Named Range

I have a pivot table which includes a Grand Total column (currently in column AU). I want to create a named range (Grand_Total_Column) for the column that contains the Grand Total that will move with the column as the pivot table expands.
Grand Total is in row 3.

I have tried using this formula in the Refers To box

=(INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH("Grand Total",\$3:\$3,0),4),1,"")))

but this did not work.

Any suggestions?

2. ## Re: Dynamic Named Range

Hi and welcome to the forum!

A Grand Total in row 3? I wasn't aware that you could set it so that the Grand Total appeared at the top of the Pivot Table by default?

In any case, it appears from the formula you are using that you are attempting to derive the cell address equivalent to a row number of 1 and a column number matching that where the Grand Total is found. But of course this single cell address will not in itself define a range: you will require the other extremity as well.

Can you clarify? Perhaps a workbook would be of benefit here.

Regards

3. ## Re: Dynamic Named Range

Assuming it will always be in row 3,
Try this

GrndTotal =INDEX(Sheet1!\$A\$1:\$ZZ\$3,3,MATCH(9^99,Sheet1!\$3:\$3 ))

4. ## Re: Dynamic Named Range

I didn't explain myself very clearly. The heading "Grand Total" of the totals column in the pivot table is always in row 3.

I've also attached an example to see if that helps to understand what I am trying to do.example.xlsx

5. ## Re: Dynamic Named Range

In your example, Grand Total is in Row 12.

6. ## Re: Dynamic Named Range

I want the Named Range to be for a column so the Grand Total is currently in column AU with the column heading in cell AU3

7. ## Re: Dynamic Named Range

may be this is what you want.

Formula:
`Please Login or Register  to view this content.`

Check the attached file:-

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1