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
This question generated 22 answers. To proceed to the answers, click here.
This thread is current as of May 20, 2014.