Excel: Dynamic Name Range

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.

For more resources for Microsoft Excel