formula for growing range

[tuktuk]

hey there,

i have a pivot table that has a distribution percentage by country.

there are currently 42 country listed.

on a second sheet i have the top ten countries listed by referencing the those top 10 on the pivot list.

i want to create a "all other" column which would the sum of the 11th to the bottom of the pivot minus one row (grand total row)

obviously this is easy but i want a forumla for the All Other to add the new countries values as the rows grow in the pivot.

current fromula is

=SUM('Paycountry Distribution'!B14:B45)

thanks

 

[jmthompson]

Couldn't you use this

=SUM('Paycountry Distribution'!B:B)-SUM('Paycountry Distribution'!B4:B13)

 

[tuktuk]

i like the approach but it has a grand total on the pivot....so the sum of the column is 2x counting the values.

hmmmmmmm

 

[jmthompson]

you may need to use VBA, then to identify the last desired row