Why doesn't the Named Range populate?

[lovecolorado]

I've been developing a spreadsheet and had it working pretty well; e.g., the named ranges always populated. I recently created a blank template version of my spreadsheet (to share with other people); but, when I populated the blank template, I discovered large parts of the spreadsheet didn't work - and I've traced the problem down to named ranges that aren't populating.

Since I have no idea how to prevent a named range from populating, I thought I would ask the Excel VBA community for help. The following screen-shot captures my view of the problem. The screen capture shows:

• the "Name Manager", with the named range "Names_Family_Members" highlighted and displaying the formula for populating the named range. The same screen captures also shows some named ranges, e.g., "Names_Family_Attendance", as populated and most named ranges, e.g., Names_Family_Members" that are not populated.
• It also shows data in the column reference by the formula for populating the named range "Names_Family_Members".

Not Populating.JPG

Can anyone tell me why the named range "Names_Family_Member" did not populate and how to fix it?

The screen capture was created from the attached spreadsheet.

[shg]

I see #REF! errors in many of the names.

[MatrixMan]

It looks like your OFFSET formula referencing the CSV file is pointing to an empty range. This is why you have (,,,) under the values column.

[lovecolorado]

Yes, I had to delete most of the file in order to post it. That is what caused the #REF! errors. However, there are no such errors in the Named Range that I pointed out.

[lovecolorado]

You are probably right, but I don't see it.

As I explained in the text of the post, the screenshot shows data in the column reference by the formula for populating the named range "Names_Family_Members". For this reason, it doesn't appear to me that my OFFSET formula references an empty range.

What am I missing?

[MatrixMan]

If you create the same range inside the CSV file do the values populate? I still think the OFFSET formula is the culprit.

[Richard Buttrey]

What do you mean by the named range is not populating? Populating where?

I've tested the Names_Family_Member name by getting VBA to print the range address and it's as I expect.

Incidentally in a dynamic range name it's usual to use COUNTA rather than COUNT although that seems to work as well.

However note the whole point of a dynamic range name is to ensure it reference just those cell it needs to and no more. So when you define

A5:A7000 in COUNT('Input - Membership CSV File'!$A$5:$A$7000) you are making it less efficient. The whole point of the COUNTA() bit is to limit the range. Personally I'd use

Please Login or Register  to view this content.

The -1 at the end is to avoid the column label

[lovecolorado]

The offset command is a standard way to read only the needed data from a list that is liable to change greatly in size (and, for different users, that list will vary in length from 100 to 5000 entries).

The offset command worked wonderfully before I deleted all of the data, created a template for others to use, and then repopulated the template. It was at that point that most (but not all) named ranges stopped populating - which is a complete puzzle to me as to why.

[lovecolorado]

Thank you for the suggestion to make the offset command more efficient. I'm just learning about dynamic ranges and appreciate the suggestion to improve speed.

Re "What do you mean by the named range is not populating? Populating where?": In the screen shot, the second column (titled 'value') lists the value of the named range - and it is empty. Instead, it should list the names in column B, below row 5.

[Richard Buttrey]

Thank you for the suggestion to make the offset command more efficient. I'm just learning about dynamic ranges and appreciate the suggestion to improve speed.

Re "What do you mean by the named range is not populating? Populating where?": In the screen shot, the second column (titled 'value') lists the value of the named range - and it is empty. Instead, it should list the names in column B, below row 5.

That's not the way Name Manager works when you define dynamic names with the OFFSET function. You'll never see the name values listed.
Presumably the designers, reasonably enough, decided the whole point of a dynamic range was that the contents of the range would change.

[lovecolorado]

I'll gladly admit that I'm learning about named ranges and dynamic lists, so the solution to my problem is probably something obvious that I'm missing. However, I can look, and I see, in the screen capture, that the named range "Names_Family_Attendees" lists the names in the range, which seems to argue against "You'll never see the name values listed."

Can you help me understand why the Name Manager shows the values in one named range, but not for others?

[Richard Buttrey]

As I said, that's because the family attendees name doesn't use OFFSET(). It's hard coded to be C15:C174, whereas the family members name does use OFFSET.

As to why MS designed it that way you'd need to ask them for a definitive answer, but I believe the one I gave you is probably the thinking they had in mind.

[MatrixMan]

Because the other one is not dynamic.

[protonLeah]

To check a name, go to Name Manager, select a name and put the cursor in the refers to box and the appropriate cell range will be selected.

[lovecolorado]

Right. And when I did that, the list of family names was selected - exactly as it should be.

Now what should I do to resolve this?

[Richard Buttrey]

Right. And when I did that, the list of family names was selected - exactly as it should be.

Now what should I do to resolve this?

You've lost me now. Resolve what?

Your OP was merely asking why the name did not populate, and after a few posts we established (I think) that you were expecting the list of names to appear in the Name Manager window - but as explained that's not the case with OFFSET.

Please explain exactly what you are trying to achieve. As Ben says in #14 clicking the name drop down in Name Manager will just confirm what the range is. It's there merely to remind you. Nothing else happens. What is there to resolve?

[lovecolorado]

Without getting into too many details, I didn't process part of a previous post and your prompt got me to review those posts sufficiently closely that I found a consistent explanation for what I'm seeing; i.e., what everyone was saying. Thank you.

It also occurred to me to go back to an old version of the spreadsheet (when it was working and before I stripped out the data to create a template) and I see the same nomenclature for populating named ranges in that spreadsheet.

So, the good news is that I'll mark this post as 'Solved'. The bad news is that I'm back to starting over on why my spreadsheet doesn't work, and I can't get help from this community because the file is is too big to upload.

[Richard Buttrey]

The bad news is that I'm back to starting over on why my spreadsheet doesn't work, and I can't get help from this community because the file is is too big to upload.

We don't need a file with zillions of rows. Just sufficient to demonstrate your problem. Usually a hundred or so rows would be sufficient. So take a copy, delete all but a representative number of rows, upload it and explain how you use it and what you expect to happen.

[lovecolorado]

I don't understand why my workbook has become so large (e.g., it doesn't contain graphics), but, with only one row of data, it is over 3MB in size. Therefore, to upload it to this forum, I have to delete most of its functionality. I realize most people can create a small 'test case' that demonstrates their issue; however, my problem only occurs when I strip-down the file I've created; i.e., I don't know where, in this 3MB file, the problem occurs, so I don't know what to create for my small 'test case'.

[Richard Buttrey]

Are you sure there's only one row of data? Check with {End}{Home} on every sheet. Sometimes I've known fromulae get copied down to row 1048576.

ALternatively save it as .xlsb file. The forum permits xlsb files up to 9Mb,

[lovecolorado]

I looked for the last row of data by pressing {End}{Home} and got an odd result. That command took me to the cell AT7005 - which seemed odd, because that cell was empty.

I moved a few cells down and to the right, selected a cell, and then dragged my selection - first to column A and then to row 6 (selecting the entire area), and pressed the delete button.

I then looked for the last row of data, once again, by pressing {End}{Home} and the command, once again, took me to the cell AT7005. Since I just deleted all data above AT7005 (up to row 6), I don't understand why pressing {End}{Home} would take me there. Do you have any idea why this would have happened?


The cell AT7005 is a 'special' cell for my program. When I set-up the offset command for up to 7000 rows, the baseline for this offset was row 5; i.e., row 7005 was the last row that my program would look at. Additionally, the table I was reading went out to column AT. Therefore, AT7005 was the last row and furthest-right cell that my VBA would look at; however, there was never any data below row 3000 and I just deleted the content of every cell between row 6 and, roughly, row 7010, and to the right of, roughly, AZ. Therefore, I don't know why this cell would be selected when I press {End}{Home}.

[Richard Buttrey]

Hi,

The {End}{Home} always finds the bottom right cell that has been used since the workbook was opened.
Even if you delete all the rows and data it will still find AT7005. It won't 'remember' to update the last used cell until you SAVE the workbook. Once you've done that you'll find things have changed.

[lovecolorado]

The {End}{Home} always finds the bottom right cell that has been used since the workbook was opened.

I've never heard {End}{Home} described in that manner and it really takes away from the usefulness of finding the end of data. However, you are obviously correct in your description.

The good news is that means I've confirmed the file really is over 3 MB when I have a single line of data.

I'm currently making significant headway by comparing the performance of my 'stripped down', template spreadsheet with the original spreadsheet. If I get stuck, I'll post to the forum again. Thank you for the tip that I can upload a 9 MB .xlsb file.

[Richard Buttrey]

I've never heard {End}{Home} described in that manner and it really takes away from the usefulness of finding the end of data.

That's a subjective opinion of course and I can't see it's any less useful when one knows how Excel operates.

[lovecolorado]

I can't see it's any less useful when one knows how Excel operates

It is less useful when everyone (even forum moderators) tell users to use {End}{Home} to find the end of data.

By extension, they do not tell users to use {End}{Home} to find the most bottom right cell that has been used ever since the workbook was opened - even when a user has reported that the spreadsheet has been stripped of all but one line of data, so the implicit assumption of ever-growing amounts of data is clearly false.

[Richard Buttrey]

It is less useful when everyone (even forum moderators) tell users to use {End}{Home} to find the end of data.

By extension, they do not tell users to use {End}{Home} to find the most bottom right cell that has been used ever since the workbook was opened - even when a user has reported that the spreadsheet has been stripped of all but one line of data, so the implicit assumption of ever-growing amounts of data is clearly false.

Sorry, you've lost me again.

I explictly DID suggest that you use End Home to find the last cell after you'd mentioned you only had one row of data so I don't really understand what you're getting at. ...but we seem to have drifted a long way from your original query over Names.

[lovecolorado]

Yeah . . . it's probably best to leave it at "we've drifted a long way" and thank you for your help.