Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Dynamic Data Validation list

Hi Everyone,

I have a worksheet with a two column list on it. It looks like this:
A B
_____________________________
MakeA Red
MakeA Green
MakeA Blue
MakeB Orange
MakeB Cyan
MakeB Blue
MakeC Purple
MakeC Violet
MakeC Red

and so on.

I have a dynamic named range for each of the columns, 'Make' and 'Model'.

What I would like to do is to create a dynamic data validation list based on the selection of 'Make' so that the appropriate 'Model's are listed in the dropdown.

I have seen a number of different approaches to this on the net, but have not had any success in implementing them. Often the explanations of their workings are convoluted and confusing.

An example use would look like this:

Cell A1 has a data validation dropdown that lists the possible makes (I have a separate list that has unique entries one for each make). The user selects the 'Make' they want and the cell B1 has a data validation dropdown which lists only the models appropriate to that 'Make'.

Can anyone help me create the dynamic data validation dropdown?

Cheers

The Frog
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Dynamic Data Validation list

Look here...

http://www.contextures.com/tiptech.html#Go_D

...and expand *+Validation* to view *Dependant Lists*.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Dynamic Data Validation list

typo...

Look here...

http://www.contextures.com/tiptech.html#Go_D

..and expand *+Validation* to view *Dependent Lists*.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Dynamic Data Validation list

Hi GS,

Thanks for the link. Excellent resource. I will have a play with the options it provides and see if I can find a workable solution.

Cheers

The Frog
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Dynamic Data Validation list

Hi GS,

I have a semi-working solution based on the following:

=OFFSET(Brand,MATCH($C2,Manufacturer,0)-1,0,COUNTIF(Manufacturer,$C2))

With the Manufacturer / Brand lists the entries may not be sorted. I can do this manually of course, however I am wondering if there is a way to handle the lists in an unsorted way. The same two columns apply, Manufacturers (Make) in one column, and Brand (Model) in the adjacent column. If Manufacturer A has an entry interspersed with Manufacturer B, or Manufacturer C what I am receiving back in the Brand (Model) validation list is the correct number of entries, but the entries are from a contiguous set of cells in the Brand (Model) column rather than the scattered individual entries relevant to the Manufacturer.

I think this can be solved with an array formula but I am unsure how to proceed with this. Do you have any ideas?

Cheers

The Frog


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Dynamic Data Validation list

EDIT: The formula is for the data validation. The 'Brand' and 'Manufacturer' are dynamic named ranges.

The data looks like:

A 1
A 2
A 3
B 4
B 5
B 6
A 7
B 8
C 9

And so on... where the first column is the Manufacturer named range and the second column is the Brand named range.
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Dynamic Data Validation list

Here's how I use this methodology...

1st DV col lists Chart of Accounts (CoA);
-this is a vertical dynamic named range in ColA of a sheet named
"Lists"

Running horizontally off each item in the CoA are sub-accounts. Each
item, then, is also the name of a horizontal dynamic range.

Now, the CoA is divided into 3 main sections; Income, CostOfSales, and
Expenses. The Income list contains sales categories. The Expenses list
is further divided into OperatingExpenses and OtherExpenses.

So to give example...
A | B | C | D | E |...and so on
Income | Revenue1 | Revenue2 | Revenue3 | Income:Other
Cost Of Sales | Freight In | Freight Out | Delivery Expense | COS:Other
Expenses
Administration | Admin Fees | Management Fees | Admin:Other
Insurance | Building | Liability | Insurance:Other
Taxes | Business | Property | Taxes:Other
Bank Charges | Fees | Interest | Bank Charges:Other
OtherExpenses
Web And Internet | Admin Charges | Internet Service | Web:Other
Vehicle Expense | Repairs | Insurance | Fuel/Oil : Vehicle:Other

...where each named range is contiguous. Names are the CoA list minus
spaces so I can use the following formula in the sub-account DV...

=INDEX(SUBSTITUTE(ExpenseCategory," ",""))

...so when I select 'Bank Charges' on the Expenses sheet its dependent
DV lists the 3 items in the dynamic range named "BankCharges". The 1st
DV is in a column-absolute, row-relative range named "ExpenseCategory"
on the Expenses sheet. Its DV formula is "=ExpenseCategories" as that's
the name of the Expenses section of the CoA.

Suggestion:
List the manufacturers in colA, and list models for each manufacturer
horizontally as exampled above.

Name colA dynamic "Makes".
Name the models lists the manufacturer.

In your sheet 1st DV "=Makes".
In your 2nd DV "=INDEX(SUBSTITUTE(Make," ",""))", and name that col
"Make" so it's row-relative.

Note that these DV dynamic names need to have workbook level scope, BUT
make all sheet named ranges local scope (sheet level)!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Dynamic Data Validation list

For clarity...

Here's how I use this methodology...

1st DV col lists Chart of Accounts (CoA);
-this is a vertical dynamic named range in ColA of a sheet named
"Lists"

Running horizontally off each item in the CoA are sub-accounts. Each
item, then, is also the name of a horizontal dynamic range.

Now, the CoA is divided into 3 main sections; Income, CostOfSales,
and Expenses. The Income list contains sales categories. The Expenses
list is further divided into OperatingExpenses and OtherExpenses.

So to give example...
A | B | C | D | E |...and so on
Income | Revenue1 | Revenue2 | Revenue3 | Income:Other
Cost Of Sales | Freight In | Freight Out | Delivery Expense |
COS:Other
Expenses
Administration | Admin Fees | Management Fees | Admin:Other
Insurance | Building | Liability | Insurance:Other
Taxes | Business | Property | Taxes:Other
Bank Charges | Fees | Interest | Bank Charges:Other
OtherExpenses
Web And Internet | Admin Charges | Internet Service | Web:Other


Vehicle Expense | Repairs | Insurance | Fuel/Oil | Vehicle:Other

..where each named range is contiguous. Names are the CoA list minus
spaces so I can use the following formula in the sub-account DV...

=INDEX(SUBSTITUTE(ExpenseCategory," ",""))

..so when I select 'Bank Charges' on the Expenses sheet its dependent
DV lists the 3 items in the dynamic range named "BankCharges". The
1st DV is in a column-absolute, row-relative range named
"ExpenseCategory" on the Expenses sheet. Its DV formula is
"=ExpenseCategories" as that's the name of the Expenses section of
the CoA.

Suggestion:
List the manufacturers in colA, and list models for each manufacturer
horizontally as exampled above.

Name colA dynamic "Makes".
Name the models lists the manufacturer.

In your sheet 1st DV "=Makes".


In your 2nd DV "=INDEX(SUBSTITUTE(Make," ",""))", and name the 1st DV
col "Make" so it's row-relative.

Note that these DV dynamic names need to have workbook level scope,
BUT make all sheet named ranges local scope (sheet level)!


--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Dynamic Data Validation list

Hi GS,

I understand where you are going with this but it wont work for my data. This method relies on being able to break up the data into separate lists and then reference the separated lists. Unfortunately the data comes to me as a single long list.

I have seen a possible method using SMALL and MATCH as a way of locating the relevant rows, but I am unable to get it to work. I dont understand the the formula methodology to apply it correctly. An example of the method can be seen he

http://fiveminutelessons.com/learn-m....WAeJmH8w.dpbs

Do you have any idea how I might apply this or something similar?

Cheers

The FRog
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Dynamic Data Validation list

Hi GS,

I understand where you are going with this but it wont work for my
data. This method relies on being able to break up the data into
separate lists and then reference the separated lists. Unfortunately
the data comes to me as a single long list.

I have seen a possible method using SMALL and MATCH as a way of
locating the relevant rows, but I am unable to get it to work. I dont
understand the the formula methodology to apply it correctly. An
example of the method can be seen he

http://fiveminutelessons.com/learn-m....WAeJmH8w.dpbs

Do you have any idea how I might apply this or something similar?

Cheers

The FRog


The example you link to has nothing to do with dynamic DV lists. That's
what you asked for and is what I spoke to. You must organize your data
to be usable in a DV list -OR- you could use VBA to organize it and put
it into DV lists for you. Either way, global scope named ranges need to
be used if the lists are on a different sheet than the DVs that use
them.

You don't say how many items are in the lists you get, but regardless
of list length the data must be restructured such that it *qualifies*
for use in DV dropdowns.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Dynamic Data Validation list

As an example...

Makes |
MakeA | Red | Green | Blue
MakeB | Orange | Cyan | Blue
MakeC | Purple | Violet | Red

OR

MakeA | MakeB | MakeC
Red | Orange| Purple
Green | Cyan | Violet
Blue | Blue | Red

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Dynamic Data Validation list

Hi GS,

Thankyou for all your help. I really appreciate it.

It looks like Excel is not going to be able to do what I need it to do. There is no possibility of re-ordering and breaking up the lists on an ongoing basis in order to achieve this result. The lists will always come as columns as described, sometimes even more columns for other data sets.

In order to make this work I will need to use a database to manage the information and do the cascading selections - the good old WHERE clause.

It is a shame that Excel lacks such a fundamental feature necessary to deal with modern data sets. I cannot imagine a supplier web API being altered simply to suit Excels lack of data handling capability for what is otherwise a common way of receiving data.

On the plus side I have a copy of MS Access as part of my MS Office suite so I will do it there. I am quite happy and comfortable with databases so this is not a problem for me. I do feel sorry for those that dont have the training to build such a tool. Dynamic criteria based data validation / selection, even if it was via a UDF, would be such a standard requirement today. The UDF path will work if you want it to but only to a length of 255 characters in the list if you set the cells data validation by code. Still a god awful work-around for such a simple scenario.

Thanks again for your help. I really appreciate you trying to solve this with me.

Cheers

The Frog
  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Dynamic Data Validation list

As it happens.., you can use ADODB to handle data in Excel in a
database fashion. It treats an Excel file as a database, and a sheet
(or range) as a 'table'. The beauty of it is you do not have to have
the data source file open! See here for more info...

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #14   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

Is this what you're trying to achieve? Your question was answered on the second post.

http://www.contextures.com/xlDataVal13.html
Attached Files
File Type: zip DynamicValidationSample.zip (6.9 KB, 29 views)
__________________
Asobi Wa Owari Da

Last edited by wickedchew : February 27th 16 at 04:37 AM Reason: Forgot the attachment! doh!
  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Dynamic Data Validation list

Hi GS,

I am aware of this however it does not support the use of dynamic named ranges. When you try to use one you will receive an empty resultset / zero records. Change the named range to a fixed area and the problem disappears, change it back to dynamic and it returns. You can use ADO with many different data sources actually. Its extremely flexible and suits most anything you could want to do - except with Excel.

I am beginning to wonder whether or not this limitation within Excel is deliberate. It seems odd that this one specific functional requirement is the one that you cant perform no matter how you approach it. In the meantime I'll just use the API to suck the data into a BE dtabase and stick an Access FE on top of it. I can pull in whatever Excel functions I might need for processing or analysis and get the best of both worlds without the headaches. Good thing I am highly proficient with databases and MS Access as an FE.

Cheers

The Frog


  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Dynamic Data Validation list

Hi GS,

I am aware of this however it does not support the use of dynamic
named ranges. When you try to use one you will receive an empty
resultset / zero records. Change the named range to a fixed area and
the problem disappears, change it back to dynamic and it returns. You
can use ADO with many different data sources actually. Its extremely
flexible and suits most anything you could want to do - except with
Excel.


When using ADO with Excel named ranges, I usually pass its .Address to
overcome this deficiency. Given your sample lists, I don't see why ADO
shouldn't work in your desired fashion.

I am beginning to wonder whether or not this limitation within Excel
is deliberate. It seems odd that this one specific functional
requirement is the one that you cant perform no matter how you
approach it. In the meantime I'll just use the API to suck the data
into a BE dtabase and stick an Access FE on top of it. I can pull in
whatever Excel functions I might need for processing or analysis and
get the best of both worlds without the headaches. Good thing I am
highly proficient with databases and MS Access as an FE.


What I see commonly is that most people proficient in Access have
erroneous expectations when trying to work in Excel in any similar
fashion. I think it's a major disadvantage to MS Office users that its
components are still developed as stand-alone apps and so aren't better
integrated to work together.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #17   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

Quote:
Originally Posted by The Frog[_2_] View Post
Hi GS,

I am aware of this however it does not support the use of dynamic named ranges. When you try to use one you will receive an empty resultset / zero records. Change the named range to a fixed area and the problem disappears, change it back to dynamic and it returns. You can use ADO with many different data sources actually. Its extremely flexible and suits most anything you could want to do - except with Excel.

I am beginning to wonder whether or not this limitation within Excel is deliberate. It seems odd that this one specific functional requirement is the one that you cant perform no matter how you approach it. In the meantime I'll just use the API to suck the data into a BE dtabase and stick an Access FE on top of it. I can pull in whatever Excel functions I might need for processing or analysis and get the best of both worlds without the headaches. Good thing I am highly proficient with databases and MS Access as an FE.

Cheers

The Frog
Have you used the OFFSET function to create a dynamic named range?
__________________
Asobi Wa Owari Da
  #18   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Dynamic Data Validation list

Hi Wicked. Yes I used Offset based formula to define my named ranges.

GS: What you suggest is to use code to hard define a named range then access that range via a database connectivity tool to then try and set a data validation list by code assuming it will fit in the 255 character limit. This would require different code for each dynamic validation. Or I could just use a database and handle it in a properly controlled way. I am always amazed at people trying to force poor fit methods into excel attempting something handled far better by other tools. This is a case in point. You could probably force the data into something excel might be able to work with, involving either a lot of human effort or cobbled code making it hard to support, or do it in a standardized and supportable way with a database. I appreciate your help with this but it turns out that excel is a poor choice to solve this.
  #19   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Dynamic Data Validation list

Hi Wicked. Yes I used Offset based formula to define my named ranges.

GS: What you suggest is to use code to hard define a named range then
access that range via a database connectivity tool to then try and
set a data validation list by code assuming it will fit in the 255
character limit. This would require different code for each dynamic
validation. Or I could just use a database and handle it in a
properly controlled way. I am always amazed at people trying to force
poor fit methods into excel attempting something handled far better
by other tools. This is a case in point. You could probably force the
data into something excel might be able to work with, involving
either a lot of human effort or cobbled code making it hard to
support, or do it in a standardized and supportable way with a
database. I appreciate your help with this but it turns out that
excel is a poor choice to solve this.


No.., that's not the context of what I'm suggesting.

I suspect your opinion that Excel is a poor choice to solve this is
based on your level of skill with using Excel as a database management
solution compared with your level of skill with Access. I do in Excel
exactly what you're trying to do here as a matter of common tasking,
based on my level of skill using Excel compared to near zero level of
skill using Access. Whenever my tasks require a real database I use
SQLite when needs exceed the capabilities of text files or
spreadsheets.

IMO, it shouldn't matter what format (mdb, dat/txt, xls) your database
is since ADODB handles recordsets pretty much the same way for all. The
'catch' is in how to structure the database. Where Excel or text files
are used, new data gets added/removed 'dynamically' into its respected
fields/records/tables.<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #20   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Dynamic Data Validation list

Hi GS,

I understand what you are saying yet the fact remains that Excel cannot handle the data in the way it arrives, cannot process it as required, and therefore cannot provide a suitable solution to the problem.

If time permitted to completely refactor the data into separate chunks in order to overcome Excels lack of ability to do a conditional cascaded dynamic validation list. Effectively it lacks a WHERE clause to apply multiple conditions the results in more than a single cells worth of data being returned.

The nearest that can be achieved is something like this:
=INDEX(field_you_want-returned,MATCH(1,(criteriaField1=$B2)*(criteriaFie ld2=$C2)*(criteriaField3=$D2),0),1) as an array formula.

This of course will only return the first result encountered. There is no actual way in Excel to achieve this type of functionality returning multiple results.

I am happy to be proven wrong on this, but so far nothing that has been suggested here achieves this not have I been able to locate a suitable approach on the web. Dynamic named ranges do not work with ADO. So what is the alternative except to break the data up into lists, then lists of lists and so on in order to facilitate this. Utterly useless in this scenario.

As for my experience with Excel, I have worked as a professional developer designing and building analytical tools for global companies for years. I am quite aware of Excels abilities. My hope was that in a newer version of Excel, in this 2013, that the ability might have finally been included as a feature of the application, but it hasn't.

If you know a way to achieve the above formula with multiple results being returned such that a data validation list can be generated then I am all ears. The nearest I have managed to achieve is to use a type of secondary dynamic list for each stage of the criterion 'filtering'. The more cascades you need the less feasible it becomes such that by the time you hit a third criteria your number of supporting lists grows exponentially. Wholly unsupportable in the real world.

So, back to the point: Excel is not suited to this problem.

The Frog


  #21   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Dynamic Data Validation list

Hi GS,

I understand what you are saying yet the fact remains that Excel
cannot handle the data in the way it arrives, cannot process it as
required, and therefore cannot provide a suitable solution to the
problem.

If time permitted to completely refactor the data into separate
chunks in order to overcome Excels lack of ability to do a
conditional cascaded dynamic validation list. Effectively it lacks a
WHERE clause to apply multiple conditions the results in more than a
single cells worth of data being returned.

The nearest that can be achieved is something like this:
=INDEX(field_you_want-returned,MATCH(1,(criteriaField1=$B2)*(criteriaFie ld2=$C2)*(criteriaField3=$D2),0),1)
as an array formula.

This of course will only return the first result encountered. There
is no actual way in Excel to achieve this type of functionality
returning multiple results.

I am happy to be proven wrong on this, but so far nothing that has
been suggested here achieves this not have I been able to locate a
suitable approach on the web. Dynamic named ranges do not work with
ADO. So what is the alternative except to break the data up into
lists, then lists of lists and so on in order to facilitate this.
Utterly useless in this scenario.

As for my experience with Excel, I have worked as a professional
developer designing and building analytical tools for global
companies for years. I am quite aware of Excels abilities. My hope
was that in a newer version of Excel, in this 2013, that the ability
might have finally been included as a feature of the application, but
it hasn't.

If you know a way to achieve the above formula with multiple results
being returned such that a data validation list can be generated then
I am all ears. The nearest I have managed to achieve is to use a type
of secondary dynamic list for each stage of the criterion
'filtering'. The more cascades you need the less feasible it becomes
such that by the time you hit a third criteria your number of
supporting lists grows exponentially. Wholly unsupportable in the
real world.

So, back to the point: Excel is not suited to this problem.

The Frog


Ah.., you're trying to do this with a formula? ADO is coded so if you
post a link to a sample with the source data list and the expected
results on another sheet I'll see what I can do. If the source list
comes from a text file then include this in your link.

No degrading of your Excel skills was meant nor intended. I just meant
to explain the diff between using Access ways versus Excel ways at
different user skill levels. Also, I know award winning users of Excel
in the analystics field and most of them I consider wizards at what
they do. Haven't met any that can program Excel past macro recording.
Not saying you fit into this group of users, ..just saying!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #22   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Dynamic Data Validation list

Hi GS,

I appreciate your help. The coding isn't an issue for me. I can handle the coding side easily. The limitation comes from Excels inability to use a dynamic named range as an ADO data source, compounded by the 255 character limit on using VBA to define the validation list.

To the best of my knowledge there is no direct way to assign either an ADO recordset as a list source or an array (in memory) to get around the 255 character limit. This leaves the unenviable problem of trying to define the list in code and produce a new list for every dropdown in every column and row. Highly untenable.

Basically using Excel here I would have to put together a very awkward and functionally limited kludge, which I must avoid.

Do you know a way around this problem that avoids any of these unwanted scenarios?
  #23   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Dynamic Data Validation list

Hi GS,

I appreciate your help. The coding isn't an issue for me. I can
handle the coding side easily. The limitation comes from Excels
inability to use a dynamic named range as an ADO data source,
compounded by the 255 character limit on using VBA to define the
validation list.

To the best of my knowledge there is no direct way to assign either
an ADO recordset as a list source or an array (in memory) to get
around the 255 character limit. This leaves the unenviable problem of
trying to define the list in code and produce a new list for every
dropdown in every column and row. Highly untenable.

Basically using Excel here I would have to put together a very
awkward and functionally limited kludge, which I must avoid.

Do you know a way around this problem that avoids any of these
unwanted scenarios?


Yes.., and is why I offered. Easier to demo than explain so why I asked
for a sample file.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #24   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Dynamic Data Validation list

Hi GS. I appreciate your offer. I can't put real data "out there" so I will create a mock up with the same structure.
  #25   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Dynamic Data Validation list

Hi GS. I appreciate your offer. I can't put real data "out there" so
I will create a mock up with the same structure.


That's what I'm expecting...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #26   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 153
Default Dynamic Data Validation list

I have a semi-working solution based on the following:

=OFFSET(Brand,MATCH($C2,Manufacturer,0)-1,0,COUNTIF(Manufacturer,$C2))

With the Manufacturer / Brand lists the entries may not be sorted. I can do
this manually of course, however I am wondering if there is a way to handle
the lists in an unsorted way. The same two columns apply, ...


If this is tangential or redundant, forgive the digression.

There's a way to create a dynamic data validation list using only Excel formulas. The approach does use a lot of work space for intermediate results, though. It starts from the long two-column list, dynamically builds a two-dimensional "Make" vs. "Color" matrix in an out-of-the-way place, and uses the matrix for the data validation formulas.

It's not elegant and it doesn't use named ranges, but it does seem to fill the need.

For visual clarity, my example uses one worksheet, but the work can be split among two or more using the same basic approach.

Columns A:B hold the original data. Column A contains the "Make" values; B, the "Colors." Duplicated Make values need not be contiguous.

D1 will have data validation for Makes; D2, for Colors.

In F1, put the number 1.

In F2, put
=IF(A2="","",IF(COUNTIF(A$1:A2,A2)1,"",MAX($F$1:F 1)+1))
and copy down past the end of the original list.

In H1, put
=IF(ROW()MAX(F:F),"",INDEX(A:A,MATCH(ROW(),F:F,0) ))
and copy down as far as before.

In I1 put
=IF(H1="","",COUNTIF(A:A,H1))
and copy down as far as before.

In G1, put
=IFERROR(100*MATCH(A1,H:H,0) + COUNTIF(A$1:A1,A1),"")
and copy down as far as before.

In J1, put
=IFERROR(INDEX($B:$B,MATCH(100*ROW()+COLUMN()-9,$G:$G,0)),"")
and copy down as far as before.

Select the formula-containing cells in column J, and copy them rightward more columns than the largest number of Colors for any Make.

In D1, use the data validation formula
=OFFSET($H$1,0,0,MAX(F:F),1)

In D2, use the data validation formula
=OFFSET(I1,MATCH(D1,H:H,0)-1,1,1,VLOOKUP(D1,H:I,2,FALSE))

Changing columns A:B should update the data validations without other intervention.

(I have Excel 2010.)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic List Data Validation Bean Counter[_2_] Excel Discussion (Misc queries) 5 May 17th 10 03:35 PM
Dynamic Data Validation List Ken G. Excel Discussion (Misc queries) 2 February 1st 07 07:15 AM
data validation invalid in dynamic validation list ilia Excel Discussion (Misc queries) 0 November 7th 06 01:54 PM
data validation invalid in dynamic validation list ilia Excel Worksheet Functions 0 November 7th 06 01:54 PM
data validation invalid in dynamic validation list ilia Excel Programming 0 November 7th 06 01:54 PM


All times are GMT +1. The time now is 01:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"