Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dependant Data Validation not sorted
Hi
I have a worksheet that contains legacy data that is being cleaned to conform to specific formats and conventions. I have another worksheet that uses this cleaned data to build a new database row. I want to put a drop down list in my database row worksheet that lists only the cleaned data. For Example Column A is the data to be cleaned Column B contains a formula that tells the end user that the data is OK or what about the data needs to be cleaned. ColA ColB AA12 OK aa13 Clean Letters BB03 OK CC4 Clean Numbers So my drop down list must contain the content of ColA where the content of ColB = OK i.e. AA12 and BB03. I've tried using Debra Daglish's Dependant Drop Down Sorted Lists solution ( http://www.contextures.com/xlDataVal13.html ) with the formula changed so that the column goes to the left by 1, but as you can see, ColA has the sort. I get AA12 and aa13 in my drop down list. I have considered using Debra Daglish's Create Dependant Lists solution using the INDIRECT function, but if I understand it, every distinct occurrence in ColB's would have to have it's own named range. I can see that solution getting rather cumbersome. Can anyone suggest a solution? TIA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dependant Data Validation not sorted
Hi Sara
You don't need Data Validation for a drop down list in this scenario. You need Autofilter. Highlight cells A1 and B1, DataFilterAutofilter Choose the dropdown on column B and select OK This will give a filtered list of the rows where column A data is correct. If required, you can mark the filtered range of Rows, Copy and then Paste to another location. -- Regards Roger Govier "Sara Hopkins" wrote in message ... Hi I have a worksheet that contains legacy data that is being cleaned to conform to specific formats and conventions. I have another worksheet that uses this cleaned data to build a new database row. I want to put a drop down list in my database row worksheet that lists only the cleaned data. For Example Column A is the data to be cleaned Column B contains a formula that tells the end user that the data is OK or what about the data needs to be cleaned. ColA ColB AA12 OK aa13 Clean Letters BB03 OK CC4 Clean Numbers So my drop down list must contain the content of ColA where the content of ColB = OK i.e. AA12 and BB03. I've tried using Debra Daglish's Dependant Drop Down Sorted Lists solution ( http://www.contextures.com/xlDataVal13.html ) with the formula changed so that the column goes to the left by 1, but as you can see, ColA has the sort. I get AA12 and aa13 in my drop down list. I have considered using Debra Daglish's Create Dependant Lists solution using the INDIRECT function, but if I understand it, every distinct occurrence in ColB's would have to have it's own named range. I can see that solution getting rather cumbersome. Can anyone suggest a solution? TIA |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dependant Data Validation not sorted
Thanks for the input Roger, but that's not going to work. The corrected data
becomes just one column in as a database row. The corrected data will not be in the same order or even frequency. The database worksheet will be more like this: ColA ColB ColC ColD ColE... etc Various data BB03 other cleansed data lists Various data BB03 other cleansed data lists Various data AA12 other cleansed data lists Various data BB03 other cleansed data lists etc... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dependant Data Validation not sorted
Hi Sara
I had mis-read your question, and thought you were trying to copy the clean ("OK") rows from your sheet to another location. You should be able to use Debra's technique, but your columns are effectively reversed from Debra's layout in her sample file. Either move your column on your Data so that the O.K.'s appear in column A, rather than B, or change the validation for your drop down to the equivalent of =OFFSET(RegionStart,MATCH(B4,RegionColumn,0)-1,0,COUNTIF(RegionColumn,B4),1) having re-defined Region Start to $B$1 instead of $A$1 (Note. The above is what you would do if you moved column B on Validation Lists of Debra's file, to column A, which is the effectively the same situation as you have) Hopefully from the above, you can work out what you need to do with your file. -- Regards Roger Govier "Sara Hopkins" wrote in message ... Thanks for the input Roger, but that's not going to work. The corrected data becomes just one column in as a database row. The corrected data will not be in the same order or even frequency. The database worksheet will be more like this: ColA ColB ColC ColD ColE... etc Various data BB03 other cleansed data lists Various data BB03 other cleansed data lists Various data AA12 other cleansed data lists Various data BB03 other cleansed data lists etc... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation | Excel Worksheet Functions | |||
data validation | Excel Worksheet Functions | |||
Data Validation | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |