Help with Dynamic Named Ranges

[mleosu]

From my preivous thread about GetPivotData (

http://www.tek-tips.com/viewthread.cfm?qid=1368354&page=1)

I found I need to use dynamic named ranges - and looked at

http://tek-tips.com/faqs.cfm?fid=1331

for instructions. I followed them, but I guess I am totally confused by them to. I keep getting errors on my naming code. Help!

 

[SkipVought]

Hi,

It might help if we could see the code that is not working.

Please post and explain the nature of the range you are naming; where located, # columns, # rows, etc.

Skip,

_[red][/red]

 

[AnotherHiggins]

Juding by your previous post, you're dealing with a Pivot Table.

Do you use the same table each time and just refresh? If so, great! What is the top left cell of the pivot table? (The top-left most cell that produces the blue outline around the Pivot Table)

Let's say it is A3 on Sheet1. You would:
[ul][li]go to Insert > Name > Define[/li]
[li]Enter in a Name for the range. Let's say rngPivot1[/li]
[li]In the Refers To box, type in[/li]
[ul][li]=offset(Sheet1!$A$3, 0, 0, counta(Sheet1!$A:$A), counta(Sheet1!$4:$4))[/li][/ul][/ul]

The arguments for the OFFSET function are:
=Offset(Anchor,RowsDown,ColumnsOver,Height,Width)

You might need to tweak the formula I provided based on what your pivot table looks like.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[mleosu]

Let's see if I got this close to right...

Here is the offset formula I entered:
=OFFSET('Pivot Tables'!$J$3, COUNTA('Pivot Tables'!$J:$J), 0, 0, 8)

Here is a picture of my worksheet (

http://farm1.static.flickr.com/189/501266952_44010a846d.jpg)

- notice though that there is another pivot table below the one I am working with in the offset - I am afraid the offset formula will count the cells in that pivot table as well with the COUNTA('Pivot Tables'!$J:$J) for the rows down, which would not be good. There are about 9 pivot tables currently on this worksheet - in a matrix of 3x3 (so there is a 3rd pivot table below the 2 you see here).

I was going to list them one by one across the spreadsheet - but I dont think I will have enough room. And I had split them up into 3 worksheets - but I have too many tabs already! I dont want my workbook flooded with tabs when I can consolidate.

Please advise. Thanks!!

 

[mleosu]

by the way. what is the differene between columns over and width?

 

[SkipVought]

Hi,

Here are some things to consider...

1) understand the 5 arguments
1 - anchor reference
2 - row offset from anchor reference (this is a NUMBER)
3 - column offset from anchor reference (this is a NUMBER)
4 - # of rows in return range (this is a NUMBER)
5 = # of columns in return range (this is a NUMBER)

here's your formula...
[tt]
OFFSET('Pivot Tables'!$J$3, COUNTA('Pivot Tables'!$J:$J), 0, 0, 8)
[/tt]
1 - the anchor reference is 'Pivot Tables'!$J$3
2 - the row offset is COUNTA('Pivot Tables'!$J:$J) the NUMBER of non-blank cells in column J?????
3 - the column offset is 0
4 - there are ZERO rows in the return range???
5 - there are 8 columns in the return range

You have problems in your arguments.

2) Forthermore, it seems from the screen shot & explanation, you are stacking these tables on one sheet. Doing that, there is NO WAY that OFFSET if going to return the correct range!!! The COUNTA function needs to count the occurrences in ONE TABLE. Seems J:J is spanning multiple tables.



Skip,

_[red][/red]

 

[mleosu]

Okay - thats what I thought after I looked at the counta, i will work on moving the pivot tables to not stack. and i will work on the offset formula and come back.

thanks.

 

[mleosu]

OF COURSE - if I move my pivot tables to be side by side - then i can keep using the vLookup and include the entire columns so it will grab everything - then i wouldnt need the dynamic named ranges - right?

but i want to learn them anyways - so i will be back after i move the tables.

 

[AnotherHiggins]

[Edit]
Looks like I'm a bit late to the party. But I think this still might help, so I'll post it anyway.
[/Edit]

RowsDown and ColumnsOver are used to define the top-leftmost corner of the range.

Height and Width are used to define how tall and wide the range is.

Example:
=OFFSET(Sheet1!$A$1, 1, 1, COUNTA(Sheet1!$A:$A), COUNTA(Sheet1!$1:$1))

would define the following range:

It works like this:

=OFFSET(
[tab]Sheet1!$A$1, [green]<-Where to start - not necesarily part of the range[/green]
[tab]1, [green]<-How many rows down to start the range (top left corner)[/green]
[tab]1, [green]<-How many columns over to start the range (top left corner)[/green]
[tab]COUNTA(Sheet1!$A:$A), [green]<-How many rows tall[/green]
[tab]COUNTA(Sheet1!$1:$1)[green]<-How many columns wide[/green]
)

Place each Pivot Table in it's own sheet. This is the default behavior if you use the Excel Pivot Table Wizard.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[mleosu]

okay - here is the new sheet

http://farm1.static.flickr.com/215/501332917_a1de51ed69_o.jpg

this is the offset i have written:
=OFFSET('Pivot Tables (2)'!$A$3, 0, 0, COUNTA(Sheet1!$A:$A),8)

=OFFSET(
Sheet1!$A$1, <-starts at begining of pivot table
0, <-will start w/ header rows(didnt see any reason to offset)
0, <-will start in column a (so that i can get the branch number)
COUNTA(Sheet1!$A:$A), <-will count how many rows for the pivot table
8 <-- will always be 8 columns wide
)

 

[mleosu]

oops = that should be COUNTA('Pivot Tables(2)'!$A:$A),

 

[mleosu]

works like a charm!

thanks for all your help!

 

[SkipVought]

it's a tag team, John! ;-)

Skip,

_[red][/red]

 

[AnotherHiggins]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.