drop down list to progressively filter

Posted by Johnny C. Usry on May 29, 2001 12:53 PM

I have a list of time codes that has 730 rows. The first column contains a category, with from 5 to 15 duplicates for each category. The second column contains a work description with occasional duplicates, and the third column contains a unique code for each row. What I would like to accomplish is to be able to use data validation (or something similar) to populate the cells. I would like to choose a category in the first column from the drop down list, and when I move to the next column, the drop down list should only display descriptions that match the category chosen in the first column. When I choose a description, the code in the third column is populated with the unique value that matches the first 2 columns. Is there a way to accomplish this?

Posted by Connie on May 29, 2001 1:42 PM

In order to find a solution for you, may I ask how the sheet is being used? Is it for your own use or multiple users? Is the ultimate purpose to be able to drill down to the specific code that matches category and work description? Would it hurt the integrity of your data to do a sort and eliminate the duplicates before going further? Or must the duplicates remain?
Thanks-Connie

Posted by JAF on May 30, 2001 6:32 AM

I have a file which does what you need, but only at one level. There's no reason why you can't amend it to your requirements.

I'll email you the file tonight when I get home from work.

I was assisted in this from this message board some time ago, I think the topic was "Variable Data Validation" - search for that from MrExcel main page for further pointers.

JAF

Posted by Johnny C. Usry on May 30, 2001 7:25 AM

Posted by Aladin Akyurek on May 30, 2001 7:33 AM

Johnny,

I don't know what Connie will make out of this additional info, I think I still don't understand the problem. I thought your problem might have some similarity to one posted at

http://www.allexperts.com/BoardDetail.asp?MessageID=609550&listpage=1

Would you care to check this and if so, check my answer to it in a follow-up in that thread?

Aladin

:The sheet is to be used as a daily time sheet for multiple users, the purpose is to populate each entry on the time sheet with category, description and code without manually searching all 700+ records. The duplicates are not necessary in the list, but are necessary in the database, as there are multiple items per category, and occasional duplicate descriptions, based on category. : In order to find a solution for you, may I ask how the sheet is being used? Is it for your own use or multiple users? Is the ultimate purpose to be able to drill down to the specific code that matches category and work description? Would it hurt the integrity of your data to do a sort and eliminate the duplicates before going further? Or must the duplicates remain?

Posted by Aladin Akyurek on May 30, 2001 9:16 AM

Could not find the thread you're referring to...

and who assisted you, JAF?

Thanks.

Aladin

============== I have a file which does what you need, but only at one level. There's no reason why you can't amend it to your requirements. I'll email you the file tonight when I get home from work. I was assisted in this from this message board some time ago, I think the topic was "Variable Data Validation" - search for that from MrExcel main page for further pointers. JAF

Posted by Connie on May 30, 2001 12:05 PM

Johnny,
Is there some reason you can't just use the Auto-Filter (Data>Filter>Auto-Filter)function? If the user begins filtering at Category, then in the next column, only those descriptions associated with the category they picked will be displayed, and likewise, once they have picked a work description, only the code associated with that description will be displayed in the third column. The auto-filter choices are basically like drop-down cells. Would this work for you?
Connie

Posted by Johnny C. Usry on May 30, 2001 2:25 PM

This is very close to what I need. On your IBoard page, If I choose hardware,instead of all of the matches in column C being displayed, I would like for those matches to be in a list box. After choosing the second filter, then the final column would be populated. Johnny, I don't know what Connie will make out of this additional info, I think I still don't understand the problem. I thought your problem might have some similarity to one posted at http://www.allexperts.com/BoardDetail.asp?MessageID=609550&listpage=1 Would you care to check this and if so, check my answer to it in a follow-up in that thread? Aladin

Posted by Johnny on May 30, 2001 2:33 PM

This would work if I only wanted to look up the code, but I wanted to use what was retrieved for data entry. I will also be entering multiple rows, and I have only been able to apply auto-filter one time in a worksheet. Johnny,

Posted by Aladin Akyurek on May 30, 2001 2:54 PM

Progressive filtering

Johnny,

In believe the extension that you figured out is doable. Give me some data I'll try to make that extension. A qualifying snippet would do.

Aladin

Posted by Connie on May 31, 2001 6:31 AM

Sorry Johnny, I'm stumped.......Aladin, further ideas?

Johnny,
I understand now what you're trying to accomplish. I thought perhaps you could still use auto-filter to drill down to the data you want, then use a macro to copy/paste the data onto an entry sheet, but I couldn't get it to work. I'm sure there is a better way. Did you look at Aladin's post on the other web page he suggested? Rather complex, but do you think it would work for you? Sorry I couldn't be of more help.
Connie