Dynamic Name Range

[dsandoz116]

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

 

[Jonmo1]

Is column A a formula? If yes, what is that formula?

FYI, formulas that return "" are NOT blank, and will be counted as Not Blank

 

[dsandoz116]

Yes Column A is a formula.

=IF('P:\ABC\[ABC-1.xlsx]ABC-1'!A6="","",'P:\ABC\[ABC-1.xlsx]ABC-1'!A6)

This is referencing the region specific workbook.

 

[Jonmo1]

I would put a formula in the book to do the counta based on the column that formula refers to..
Assuming THAT column is actually hard coded values, and not blanks.

Say Z1 in your book
=COUNTA('P:\ABC\[ABC-1.xlsx]ABC-1'!A:A)

Then in your named range, replace the counta function with $Z$1



Or is there any other column in your book that does not contain formulas and can be used to determine the # of rows in the named range?

 

[dsandoz116]

I put the counta formula in cell A3 in the worksheet in my summary workbook. There are over 200 rows of data and when I hit enter a value of "3" results in the cell I entered the formula. I really apologize for having you step me through this, but it is really appreciated.

Ok, so I put the formula in A3 and the "3" resulted, then I replaced the named range with $A$3. When I check to see what cells are being referenced, only three rows are highlighted, corresponding to the "3" I presume. I feel like we are so close! THANK YOU THANK YOU for helping meee!

This is what is in my named range:

=OFFSET('ABC-1'!$A$5,,,'ABC-1'!$A$3,41)


FOUND ERROR.....Here is what I edited to put in A3, my overlook.....=COUNTA('P:\ABC\[ABC-1.xlsx]ABC-1'!A:A)

Now 206 results and not 3.


Still same error is resulting even though I am getting the 206 in the cell. So frustrating, but progress is being made.

 

[Jonmo1]

You did it correctly.

If =COUNTA('P:\ABC\[ABC-1.xlsx]ABC-1'!A:A) returns 3, then it means there are only 3 non blank cells in that column.


Forget about the other book for now.
In the book your working on, is there ANY other column that contains normal hard coded values (not formulas) that can be used instead of A ?

 

[dsandoz116]

No, A through AO all contain formulas.

 

[Jonmo1]

Try

=OFFSET(ABC-1!$A$5,,,COUNTIF(ABC-1!$A$5:$A$1000,"?*"),42)

 

[dsandoz116]

Gives me a formula contains error. What if I insert a column that looks at the former column A (now B), and returns its value. Then I could count the nonblank cells this way. Tried this alternative method as well and get 995 in the cell instead of 206.

Dsandoz116

 

[Jonmo1]

The basic problem is that formulas are NOT blank, even a formula that returns ""
So the counta Function includes those cells in it's count.

Hmm, did not know this but apparently you need ' around a sheet name containing a hyphen.
I knew it was true for spaces in the sheetname, but never knew about hyphens..
Learn something new every day.

Should be
=OFFSET('ABC-1'!$A$5,,,COUNTIF('ABC-1'!$A$5:$A$1000,"?*"),42)

 

[dsandoz116]

Formula error went away. It is referencing exactly the non blank cells. All looks good until I go to the pivot table sheet and insert a pivot table. I insert N (what I named the range) for range and click on a cell where I want the pivot table. I get data source reference is not valid error.