Dynamic Name Range

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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
I was trying to reference the specific worksheet. The formula should read like this:
=OFFSET('ABC-1'!$A$6,,,COUNT('ABC-1'!$A$6:$AO$1000),42)

I think the + got in there when I hit the left or right key. Sorry.
 
Upvote 0
Your COUNT function is counting 41 columns and 995 rows
COUNT('ABC-1'!$A$6:$AO$1000)
It should probably be counting only in a single column

Change that to reference only a single column that contains the numbers you want to use to dermine how many rows are in the range
COUNT('ABC-1'!$A$6:$A$1000)
 
Upvote 0
Can you describe "did not work" ?
Did you still get the same error? Or a different error?
Does the result not correctly refer to the range you desired? What range DOES it end up referring to?
Do you actually have numbers in the range A6:A1000 ?
 
Upvote 0
My apologies, I should have included more. I am getting a "data reference source not valid" message still. Here is the formula I am using in name manager:

=OFFSET(ABC-1!$A$5,,,COUNT(ABC-1!$A$5:$A$1000),42)

I do have data in column A. This spreadsheet, along with all others of the total of 20 I have, is where regional managers enter their sales data. Column A of these spreadsheets has "Type" and contains text. When I go into Name Manager and click the workbook button to test and see what cells are being referenced, it does not take me to sheet "ABC-1".

dsandoz116
 
Upvote 0
This may be the problem
Column A of these spreadsheets has "Type" and contains text.

The COUNT function counts NUMBERS, not text.
So the count function is returning 0
And a range created by offset CANNOT contain 0 rows, must be at least 1.

Try changing that to COUNTA (counts NON Blank Cells, including text)
 
Upvote 0
I changed it to COUNTA and now when I go to the Name Manager and test it, it will go to the correct sheet, but is highlighting all 1000 cells, not just the ones with text in Column A (text stops in row 208 on this sheet, but all 1000 are highlighted). I tried inserting a pivot table then using this newly revised dynamic name range, and get the same data reference source is invalid error.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top