[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

Data validation with lists

Dear Experts,
I'm using quite often the data validation option in Excel with the help of data listings. Usually I have my listings in a hidden sheet defined and use them on the other sheets where ever needed.
All works fine as long the defined lists are fixed, (not changing).
But in real live I have quite often dynamic lists, they are changing in the amount. In the beginning the list might have 5 or 10 entries but later it grows and need to have 15 or 20 entries.
Currently I solve this manually by opening the hidden sheet, extending the range, closing it again.
Another way is that I define right away a bigger range, let's say from 1 to 10 although I have in the beginning only values from 1 to 5. But in that case I have empty rows in the pull down list and this is neither very nice.

I'm looking for a clever solution to handle the dynamics here. E.g. I could define right away a huge range for the "maybe" values on the hidden sheet if there is a possibility to have the empty space not listed in the pulldown listing or if it would be possible to have the listing starting on top when opening and not on the buttom. See attached file.
Maybe there is a complete other and much better solution out?
It is very helpful and user friendly to have the pull down listing validation but not very user friendly to maintain them. Hope there is a nice solution out.
Thank you in advance for your help and suggestions
NIls
Listing.jpg
0
Petersburg1
Asked:
Petersburg1
2 Solutions
 
Chris BottomleyCommented:
Strongly suggest you check out:

http://www.contextures.com/xldataval02.html#Dynamic

Dynamic ranges grow with the data set

Chris
0
 
dlmilleCommented:
I'm currently working on an article, leveraging part I here http:/A_5062.html.

In the sample worksheet, note the Example 4: approach - Data Validation lists, where you define a range name where the list resides.  That range could be a column in a database - even with duplicates.  This approach provides a DV list without duplicates.

So - if you have a database you're using where you're pulling a list to the side - you don't have to do that anymore.  I've tested this approach in several questions over the last month or so, with good success.

Here's the sample spreadsheet and in a sec, I'll pull up the questions I answered with similar methodology so you can see it , in action...


And here's an example of a named range that is dynamic for column A where range A1 is header, and you're dealing with text:

=$A$2:INDEX($A:$A,MATCH(REPT("Z",20),$A:$A))

or if your list is numeric, then this

=$A$2:INDEX($A:$A,MATCH(99^99,$A:$A))

Cheers,

Dave
Using-Active-X-List-Objects-or-D.xlsm
0
 
dlmilleCommented:
Here's one with cascading lists, I developed, using an Access Database:  http:/Q_26985378.html

Here's a cascading list one I developed using ActiveFilter: http:/Q_26954956.html

Let me know if you're intrigued / interested and I'd be happy to answer any questions about applying the tool to your project....

Cheers,

dave
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
dlmilleCommented:
What's the value of all this?  Aside from the cascade examples which are a bit different from contextures, re: sources of data, and using the activefilter, etc., it eliminates duplicates on the fly when your DV list is connected to any range.  No need to maintain separate lists...  In fact, I started with a contextures example, then built in the rest...

-------------------------

Oh - and here's an add-in version that provides support just for DV lists.  Just create a DV list and set it to a range name (see prior comment on dynamic ranges, etc.) and it does the dynamic data validation for you...

Just load the add-in after you pull up a spreadsheet.  There's an Addin's menu item called Dynamic_DV with an option to "Initialize", if for some reason events go awry.  It adds a temporary combobox (hidden) on any sheet you're working on.  After "Initialize", it will do its work from that point, forward.

You'll want to go into "Design" mode if you're doing spreadsheet manipulation and selecting Data Validation cells, moving them around, etc.

To go into "Design" mode, just click the Developer's Ribbon, and hit the triangle.  Hit it again, when ready to go, again...
Using-Active-X-List-Objects-or-D.xlam
Cheers,

Dave
0
 
dlmilleCommented:
Ok - I'm probably beating the proverbial "dead horse" and providing an overwhelming set of examples, but here's one more, where I created VLOOKUP like functionality for dependent lists, so cascades can be done this way, as well...

http:/Q_26929919.html

Dave
0
 
Petersburg1Author Commented:
Dear Experts,
what a rich feedback! Crazy!!! Seems to be a very hot topic. All this information are far too much for me and I feel somehow smashed to the wall :-)
Concerning dynamic drop down list it looks like "Xtermie" last link was leading me partly to the solution I'm looking for....
Also very helpful the link from Chris.
However it seems, I'm already stuck with the very simple thing, the "INDIRECT() command to create depended listings. What in the hell I'm doing wrong that it does not work properly? Please have a look to the sample file
thanks
NIls


listvalidation.xlsx
0
 
Petersburg1Author Commented:
Please see also this pdf file attached.
I figured it out to create a dynamic range with the OFFSET command but why when opening the pull down list the cursor is located at the very end of the list and not at the beginning?
Is there a way (without coding) to fix that?
See pdf.
thanks

Listing.jpg
0
 
Petersburg1Author Commented:
Sorry wrong file
Listingfields.pdf
0
 
dlmilleCommented:
Can you post a small sample with this problem?  Share your offset formula?

Is there some reason you didn't try the dynamic range formula I provided?

Its here:  http:/Q_27014320.html#a35687618

I wasn't trying to see what stuck on the wall.  I was sharing tried and true methods.  If you don't need the system to manage your list, or duplicates handled, then great.  But, I did provide a formula for dynamic range.

Try it - and in the meantime share more - your formula, and example with the results you're getting.

Dave
0
 
Petersburg1Author Commented:
Hi Dave,
all very interesting and also helpful, it is just a little bit too much and partly also too difficult for me.
With the help of all this feedback and links to even more information I finally found the solution for me.
It is a dynamic list limited to visible values listed:
It looks like this way and does fulfill all my requirements so far:

=OFFSET(Internal!$B$2;0;0;COUNTIF(Internal!$B:$B;"?*");1)

I was reading also about the index solution but did not fully understand it and also could not get your formulas working.

How the index formula must look like to get the same result the above offset formula is producing?
What is the advantage of the index formula?
Could you explain the function of the formula? Why REPT("Z",20)
the entire formula is too "high" for me...would appreciate help

=$A$2:INDEX($A:$A,MATCH(REPT("Z",20),$A:$A))

or if your list is numeric, then this

=$A$2:INDEX($A:$A,MATCH(99^99,$A:$A))

Moreover  it seems, I'm already stuck with the very simple thing, the "INDIRECT() command to create depended listings. What in the hell I'm doing wrong that it does not work properly? Please have a look to the sample file (see upper entry from Petersburg)
thanks

0
 
dlmilleCommented:
Dude - using the DV list utility I created is supposed to make life simpler, not the opposite - unless you want to understand all the lines of code, they're just pretty much plug and play (re: the add-in), lol....

Ok - I'll give up on that - this will be educational for both of us.  I'm learning already from you!

------------------------

The index formula is easier for me to write and to me is less complicated, though OFFSET has its purposes as well.  

The range starts from A$2 and then the right side of the formula searches for the largest string, or number, which doesn't exist, so it finds the next match, which is the bottom of the range.  

=$A$2:INDEX($A:$A,MATCH(REPT("Z",20),$A:$A))



You need to follow the directions verbatim in the Contextures tip.  Subordinate ranges need only have the first cell in the range named.  E.g., "Fruit" should only reference range M12.  And you need FruitCol and VegetableCol named ranges for the columns where they reside.

Here's your DV formula in this example, and the fixedPoint:INDEX method could be coded to return the same, but let's stay with the program/tutorial:

=OFFSET(INDIRECT($F11),0,0,COUNTA(INDIRECT(F11&"Col"))-1,1)

-- Nice approach to cascading lists, however drawbacks include: data from database when keys are unknown until the data is loaded - this is where my dynamic DV comes in handy.  So keep that in mind!

There are two attachments - one base, and the addin again.  After you are satisfied with the first, load the add-in and see what happens!

In the first, there are two Apple fruits, and you'll see that in the dropdown.

Using my tool, (just load the add-in then click in the DV list), you don't get the duplicates - AND you have the added benefit of the data being sorted...  (ok - I didn't give up - just trying to show simplicity of use).




listvalidation-r1.xlsx
Using-Active-X-List-Objects-or-D.xlam
0
 
dlmilleCommented:
>>also could not get your formulas working.


Ok - here's a dynamic range for the Vegetables - example purposes only - stick with what you have, but its better to understand a few ways of achieving the same outcome.

=$N$12:INDEX($N:$N,MATCH(REPT("Z",20),$N:$N)) - I find this MUCH easier to use, once I get the hang of it, than OFFSET - but that's just me.

So - create a DV list and put this in and you'll get the Veggies!


Enjoy!

Dave
0
 
Petersburg1Author Commented:
Hi Dave,
thanks a lot. A big help again. Did not have time to review all but the listvalidation-r1 file was already a great help...tomorrow more and points too :-)
thanks
regards from St. Petersburg, Russia
Nils
0
 
Petersburg1Author Commented:
Good Morning Dave,
now I have checked again all the details and again Thanks! Yes, the INDEX formula works fine as well and you are right, maybe even simpler and better.
I also downloaded your add in and installed it to see the advantages of sorting and not listing duplicates what is for sure a big advantage...however I had serious crash down of Excel 2007 including service packs with that!? I tried even 3 times but not way....so I had to disable it again.
Why is that?
Regards
Nils

addin-crash.jpg
0
 
Petersburg1Author Commented:
Thank you so much!
0
 
dlmilleCommented:
Not sure.  Try once more, but open the add-in AFTER you start Excel with a blank workbook, NOT as the first thing to open.  

Let me know.

Dave
0
 
dlmilleCommented:
Dude - sorry about the crashing Error.  I was getting it, too.

This is tested - no crashes.  Please let me know how it works for you!

Thanks!

Dave
Using-Active-X-List-Objects-or-D.xlam
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now