Comments on IT Groundhog: Of pivot tables, named ranges and excel tantrums

My refresh data functionality is gone as well. It ...

2009-08-26T15:24:08.948-07:00

My refresh data functionality is gone as well. It has become a static range rather than dynamically created from microsoft query.

I had a problem, and I can't seem to get around it...

2009-01-30T18:07:00.000-08:00

I had a problem, and I can't seem to get around it.

I created a named range called "Data" in "Book1.xls". I created a new workbook "Book2.xls", and created a pivottable in Book2. The Pivottable uses the data in the range Book1.xls!Data. The pivottable was created without a problem.

Next, I closed both files. Then I reopened Book1.xls. Then I opened Book2.xls. The pivottable in Book2.xls will no longer refresh and states that the reference to "Book1.xls!Data" is invalid.

Have you encointered this error, and is there a way to get the pivottable to recognize the named range in Book1.xls?

Thanks - great help.

2009-01-15T07:41:00.000-08:00

Thanks - great help.

New pivot table question. Has anyone seen Formula...

2008-12-05T06:47:00.000-08:00

New pivot table question. Has anyone seen Formula1,2,3... columns magically pop up when trying to manually move columns/rows within a table?

Yes, I lost the "Refresh Data" funcationality for ...

2007-11-21T06:19:00.000-08:00

Yes, I lost the "Refresh Data" funcationality for the source data!! :(

I exactly wanted to refresh the table based on the update from source data.

Now i got to start all the way again, i hope i get(in google search) someone like you to assist with this..

Thanks a zillion for ur help.

-KK

Are you doing a "refresh data" on the pivot table,...

2007-11-20T20:36:00.000-08:00

Are you doing a "refresh data" on the pivot table, or the source data?

I know that for my worksheets, I have to first refresh the source data tab and then manually go through and refresh each pivot table or it's related chart.

(there is a way to set all the pivot tables to refresh their info on startup, but I'm a control freak and want to KNOW when it's refreshed...)

OR...

Have you lost the ability to refresh your source data itself..?

If that's it, I can only suggest offering sacrifices at the almighty oracle-of-google...

It Worked!!!!But its giving me new problem now.I a...

2007-11-20T15:09:00.000-08:00

It Worked!!!!

But its giving me new problem now.
I am not able to do "Refresh Data" in the spreadsheet so that it can get the latest data from the Access DB.

The "Refresh Data" icon is now disabled.

Can you help me to sort that?

Thank you so much for your assistance.

Regards,
KK

KK:I tried creating a demo spreadsheet for a sheet...

2007-11-20T11:47:00.000-08:00

KK:

I tried creating a demo spreadsheet for a sheet named "Data Range" and I got the same error.

While it _SHOULD_ catch this when entering the range in the "refers to" section, it doesn't seem to.

I surrounded "Data Name" in _single_ quotes in the "refers to" section and it worked.

Like so:

=OFFSET('Query Data'!$A$1,0,0,COUNTA('Query Data'!$A:$A),9)

Let me know if it worked..

=OFFSET(Query Data!$A$1,0,0,COUNTA(Query Data!$A:$...

2007-11-20T10:59:00.000-08:00

=OFFSET(Query Data!$A$1,0,0,COUNTA(Query Data!$A:$A),9)

It did not allow to enter $A$A inside counta(), so i used $A:$A

In the last two columns out of my total columns(9), i use a formula to manipulate on the queried data from Access 2003.

I used "Name" as a name for this dynamic range.

I tried different names also, it didnot work.

Thank you for ur time Aaron.

Please let me know if u need any other information.

Regards,
KK

Hmm...What is the line that you used to define the...

2007-11-19T14:54:00.000-08:00

Hmm...

What is the line that you used to define the range?

The bit you typed in to the "refers to" section...in the example above, I used:
=OFFSET(sheetname!$A$1,0,0,COUNTA(sheetname!$A$A),14)

What is yours?

And what is the actual name you used for the range?

Yes Aaron,I have a spreadsheet that derives data f...

2007-11-19T12:09:00.000-08:00

Yes Aaron,

I have a spreadsheet that derives data from an Access 2003 query.

In the same spreadsheet i have a pivot table that is based on the derived data from Access.

I was able to define the name range and when i enter the same in pivot table data range, it says "reference is not valid"

Can you help me out please?

Regards,
KK

When you type the "range name" into where?Into the...

2007-11-14T14:16:00.000-08:00

When you type the "range name" into where?

Into the spot in the pivot table wizard where you specify the data range..?

Awesome!!!But im getting invalid referrence when i...

2007-11-14T13:49:00.000-08:00

Awesome!!!
But im getting invalid referrence when i type in the "Range Name"