Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I think you want to use the INDIRECT function to lookup the value of a cell, rather than define a lookup range directly in the data validation. Have a look at <a href="http://www.dicks-blog.com/archives/2004/05/11/conditional-data-validation/" rel="nofollow noreferrer">this page</a> which explains it fairly well.</p> <p>Be warned though that multiple cascading validations like this have particular problems of their own. Chiefly, if you make a second (or third etc.) selection, then go back and edit the first selection, there is no automatic flag to say that the selection may now be invalid. For example, if you have the following:</p> <pre>Type Detail Fruit Apple</pre> <p>...and then change the first column:</p> <pre>Type Detail Vegetable Apple</pre> <p>...then you may now have a problem. Finding such inconsistencies can be very hard, or requires some code to highlight/fix. The DDoE blog shows one method to highlight such errors using conditional formatting, but this is very calculation intensive and doesn't scale well.</p> <p>Be aware that multiple lookups of this nature can bring a workbook to its knees if used liberally, even without using conditional formatting.</p> <p>All in all, for a small project where data entry is tightly controlled this is a flexible method of cascading drop down lists, but if you have little control over the users, or if the data collected will grow past a few hundreds lines then you'll probably want to look at validating the validation (!) using VBA, or just going the whole hog and forcing entry through a userform, which is by far the best option.</p> <p>Edit: <a href="http://www.2shared.com/file/5163775/151deda6/SO_-_adolf_garlic.html" rel="nofollow noreferrer">Example workbook</a></p> <p>Edit again: If the data absolutely has to stay the way it is, then an advanced filter which creates the named ranges used in the validation would be required. At this level of complexity though you'd surely be better off just using a form for data entry.</p>
    singulars
    1. This table or related slice is empty.
    1. POxls cascading lookup based on pivot table style datasource?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USLunatik
    1. USLunatik
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThat example doesn't seem to restrict the choice of the next value based on the previous one, or am I missing something?
      singulars
      1. PO
      1. USadolf garlic
    2. COYes, it requires a few ranges to be defined, but should give you a few pointers. I've added a link to a workbook that hopefully helps.
      singulars
      1. PO
      1. USLunatik
    3. COThat is the behaviour I'm after but it will take too long to set up as is. The underlying requirement is actually to allow users to select db->table->column for a reporting requirements template. In order to do it as shown, I will need to set up a range per table and there are hundreds.
      singulars
      1. PO
      1. USadolf garlic
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload