Indirect an indirect for name manager for data validation

**2ruble** · 03-30-2016, 12:32 PM

I'm trying to create a dynamic drop down with indirect lists. Example, A1 is ANT., so B1 in data validation is list - =indirect(a1). my "ANT." list is on sheet 2 for a1, the contents of that cell are sheet2!b1:b10 so the name manager refers to =indirect(sheet2!a1) but the value is (...) instead of the range. Looks like this:

Name Value Refers To
ANT. (....) =indirect(sheet2!a1)

^where sheet2!a1 = sheet2!b1:b10

Is there a way to create a dynamic drop down by using indirect() references in the name manager? Did I just miss something stupid?

I have 17 ranges that total about 20,000 cells, so instead of trying to go through and selecting the ranges and then naming them, I'm trying to reference the ranges with indirect() so that I only have to name and indirect those 17 cells instead of highlighting/identifying 20,000+ cells 17 ranges at a time and then naming them.

**Richard Buttrey** · 03-30-2016, 12:37 PM

Please upload the workbook and add some notes that clearly explain which ranges should be referred to by say a couple of drop down selections. These things are always easier if we can see the request in context.

**2ruble** · 03-30-2016, 02:21 PM

Here's an example of what I'm talking about; it's not mine, I can't share mine, but this should clarify what I'm asking.

**Richard Buttrey** · 03-30-2016, 03:09 PM

Sorry but I don't understand what you expect to see in the D3 drop down for any of the B3 selections. Please clarify with examples.

**2ruble** · 03-30-2016, 03:32 PM

B3 = ant. - d3 = 1;2;3;4
b3 = bat. - d3 = 5;6;7
b3 = cat. - d3 = 8;9;10;
b3 = egg. - d3 = 11;12;13;14;15
b3 = erf. - d3 = 16;17;18;19;20;21;22

**FDibbins** · 03-30-2016, 03:33 PM

naming a bunch of ranges is not that hard - or time consuming.

Give each range a heading that will become that range name
Highlight the entire range, including headings
on the Formulas tab, click Create From Selection
make sure that just Top Row is selected
click OK

You should now have a bunch or range names that include all those headings and their respective ranges

**2ruble** · 03-30-2016, 04:16 PM

I understand that naming all of them isn't hard; my point is that the data set is (actually closer to) about 40,000 lines of data; the data is referencing specific part numbers related to specific equipment. Considering the they're ANT.0001, ANT.0002 it came over as a list from a1 - a40000. Therefore, I've separated the alpha from the numeric and then created a start and count column to create a range per prefix. So it would be nice to be able to create what I'm asking about, if it's possible, so that in the future I can do it again without having to try and pull out 5k of this one, move 7k of this one, move 4k of this one, etc and then header/name them all

**2ruble** · 03-31-2016, 08:14 AM

bump......

**dflak** · 03-31-2016, 11:59 AM

See if either of these articles can help you out. They don't use INDIRECT and are based on pivot tables, so they might be more dynamic.

http://www.utteraccess.com/wiki/inde..._%28Non-VBA%29
http://www.utteraccess.com/wiki/inde...ists_%28VBA%29

**Richard Buttrey** · 03-31-2016, 02:22 PM

Are your ranges defined by the table in E1:G6 or B1:C22 in this example.

i.e. which data does your real production data have and from which you want to derive your drop down values. It's not obvious which table/list you start with and from which all else is derived.

**micope21** · 03-31-2016, 02:49 PM

Hi

Please see the file! Only took me less than 10 minutes lol

Sheet2
Number move to column C
Cell F2

Formula:

Please Login or Register  to view this content.

This is array formula? Make sure you press same time by this (Ctrl+Shift+Enter) till you see both end {} This will tell you how many rows there is.

Cell D2

Formula:

Please Login or Register  to view this content.

Same again array formula by (Ctrl+Shift+Enter) Then copy down up to 10000? If you need more than that just change the number10000 to 20000?

Look at Name Manager and Data Val too?

**2ruble** · 03-31-2016, 03:40 PM

Richard Buttrey - the range is based on a1;a2;a3;a4;a5 - the ranges in those cells are determined by the data in the tables from e1:g6 and the range for the table is =indirect(a1) [and so on]

thanks micope21, looking now

Indirect an indirect for name manager for data validation

LinkBack

Thread Tools

Rate This Thread

Display

Indirect an indirect for name manager for data validation

Re: Indirect an indirect for name manager for data validation

Re: Indirect an indirect for name manager for data validation

Re: Indirect an indirect for name manager for data validation

Re: Indirect an indirect for name manager for data validation

Re: Indirect an indirect for name manager for data validation

Re: Indirect an indirect for name manager for data validation

Re: Indirect an indirect for name manager for data validation

Re: Indirect an indirect for name manager for data validation

Re: Indirect an indirect for name manager for data validation

Re: Indirect an indirect for name manager for data validation

Re: Indirect an indirect for name manager for data validation

Thread Information

Users Browsing this Thread

Similar Threads

Data validation using indirect

[SOLVED] 2 data Range using COUNTIF with INDIRECT and MIN IF with INDIRECT,

Indirect Data Validation

data validation and indirect

Data Validation - Indirect function

Data Validation and Indirect Function

Data Validation and Indirect

[SOLVED] Data Validation with Indirect

Bookmarks

Bookmarks

Posting Permissions