Code for Multiple Named Ranges for Data Validation

**leaning** · 06-07-2011, 02:54 PM

Hello!

Dave Hawley helped another user out who wanted to concatenate two named ranges and use that new BigList as a range for data validation.

I have a named range of Shop personnel and then another for Office.

In this project, I want to be able to choose Shop + Office.

The named ranges are on the Lists sheet using this formula: =OFFSET(Lists!$A$2,0,0,COUNTIF(Lists!$A$2:$A$500,">"""),1)

How can I apply this code to my workbook? It has the BigList being created on Sheet4, but I am already using Sheets 1-21. Is there a way this code to be modified to create the BigList on a hidden Sheet1000 or something like that?

Please Login or Register  to view this content.

I appreciate the help!

Respectfully,

Lost

**tigeravatar** · 06-07-2011, 03:41 PM

Lost,

This code goes through each worksheet to see if there is a sheet named "Big Combined List". If it finds one, it uses that sheet. If it does not find one, it creates that sheet for future use and hides it:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

**leaning** · 06-08-2011, 09:59 AM

TigerAvatar,

I put your code in a test workbook, but it doesn't seem to be doing anything. From what I can see, it isn't making the Big Combined List worksheet.

Is there something I am doing wrong?

Respectfully,

Lost

**tigeravatar** · 06-08-2011, 12:17 PM

Lost,

The code only runs when the workbook performs a calcuation, so there'd have to be a cell that has a formula in it, otherwise the workbook is just changing, not calculcating. To run the code manually, change the sub name and put it in a standard module, then run it. Additionally, this line of code:

Please Login or Register  to view this content.

Makes the worksheet hidden. You can view it by right-clicking any worksheet tab and selecting Unhide.

Hope that helps,
~tigeravatar

**leaning** · 06-08-2011, 12:57 PM

TigerAvatar,

Got it.

Incidentally, in the Test3 file posted earlier, I put =Now() (to force a recalculation) in one of the cells. I closed the workbook and then reopened it. The whole thing was flickering and I had to CTRL-ALT-DEL to get it to stop.

So, I put a button on it. It is an extra step the user has to do, but they still don't have to manually enter names in that cell.

If you don't see a better way to go, I think this thread is solved.

Thanks for your help!

Respectfully,

Lost