dsandoz116
Board Regular
- Joined
- Jun 21, 2013
- Messages
- 68
I have a summary workbook that is referencing 20 other workbooks, and each workbook is then recalling the data on individual worksheets. Thus, I have 20 worksheets that I would like to then create individual pivot tables to summarize each worksheet, and have all pivot tables on one worksheet. Problem is, the data is updated daily, and when I try to create a dynamic name range I get the error "Data source reference is not valid." I have checked multiple times, and cannot find the error. Can someone help me? Here is the formula I am using for one of my sheets, the only one I have been working on since I can't get anything to work I have tried creating the pivot table without doing dynamic name range and only selecting the cells with data, the pivot table forms perfectly and I am able to group my dates as needed.
When I go to Name Manager-->New, I change the scope to be the specific worksheet I am referencing. There will never be more than 1000 rows of data, by year end it will be about 800 I think or so. There are also 42 columns, and all of these are formatted the same on each worksheet.
=OFFSET('ABC-1'!$A$6,,,COUNT('ABC-1'!$A$6:$AO$1000),42)+'ABC-1'!$A$3
I appreciate your help!
Dsandoz116
When I go to Name Manager-->New, I change the scope to be the specific worksheet I am referencing. There will never be more than 1000 rows of data, by year end it will be about 800 I think or so. There are also 42 columns, and all of these are formatted the same on each worksheet.
=OFFSET('ABC-1'!$A$6,,,COUNT('ABC-1'!$A$6:$AO$1000),42)+'ABC-1'!$A$3
I appreciate your help!
Dsandoz116