Dynamic Range using Offset, range not found for Pivot

I am getting lost in the woods and just need a bit of light shone on this. I have spent a bunch of time reading....and can't quite get there.

I have a Data sheet (gathers daily sales results and goes back in time, only 126 rows and 18 columns at present) that is now formatted as a Table to utilize the automated expansion capabilities of 2007...nice!!!

My goal is to have this Data Sheet set up so that I can utilize it to develop and update Pivot Reports going forward... Specifically Sales summaries / year/product...the usual. I'm not especially interested in Charts at this point...just want the Pivot Report to work.

I have created a defined Name for the Data Source using the OffSet function because that is what I understand to be needed so that the Pivot Report will pull from updating data?

My table name is ResTable
My Defined name is ResData
The referred to: in the ResData field is =OFFSET('Reservations all'!$A$1,0,0,COUNTA(Reservations all!$A:$A))
this probably needs some work but I am struggling at this point.
My data is a mix of numbers and text....

When I use the Pivot Wizard and type ResData, in various combinations =ResData, ='ResData', as the MSExcel List or Database source reference, I get 'Reference is not valid" error message.

Hoping my error is something obvious to someone.

I plan to copy the lastest version of the file from the DropBox: Reservations Admin Folder into the Reports folder on my desktop, to provide me the lastest data... and then rerun the Pivot... haven't been able to get to that step. I am doing it that way as we use Dropbox to share the file and I think this method is probably the easiest, quickest.

Thanks a bunch!

Since you speak of 18 columns of data, you could try to incorparate this "width" in the offset function?

Please Login or Register  to view this content.

Thanks for this, but I have had that function in and not in, doesn't make a difference....also I have picked up on the lack of quotes around the second instance of Reservations all.

Do you think it might be the name of the sheet having a space in it?

and would you mind confirming the syntax when telling Pivot Wizard where to get the Data is it ='ResData'?

thanks

jp

So have changed the name of the sheet to one with no space
and have tried specifying it in the OFFSET formula with and without quotes....(I think the sheet name is without quotes in the Forumla?)


same message, "Reference not valid" when I open a separate workbook and run Pivot Wizard from the "Where is the Data you want to use": Finder thingy.

Thanks for jumping in

jp