Hey everyone,
I have close to a 100X100 table (very rough estimation) and viewing different parts of the spreadsheet can require a lot of scrolling.
I want to create a drop down list based on the entries of Row 6 (starting from Column G). The problem is that when I do this, the drop down lists all the columns that have blanks and duplicate entries. I want a way to remove the duplicates from the drop down list. From what I have gathered it is not possible to simply just say "remove duplicates" to a drop down list, so I have to first create a second list that has everything without duplicates.
My thought is to use the Advanced Filter (Using Excel 2007) and paste a second, unique list in Sheet2. Unfortunately, I can't figure out what I need to enter into the different text fields in order to do this.
Once I have a unique list, I plan on making the drop down based off of that list. I want the unique list to include 1 blank cell as well to choose from (weird I know, but necessary because if nothing is entered, I have a Macro that will display everything but if there is an entry it will only display the data that is equal to the row 6 entry) Essentially, I want this Macro to have some kind of data security to it by only allowing the user to choose from the available entries in Row 6 after Column G.
Another capability I was hoping to have was that the drop down by dynamic. I.E. - if I add a new entry to Row 6 after Column G, it will add it to the unique list and thus the drop down without having to re-do everything.
Let me know if I need to reword / clarify anything to make more sense. I appreciate any help the community could come with.
Dan
(the second list could be listed vertically instead of horizontally if that is any easier)
Sample:
Current:
A . . . . G _____H ____ I ____ J _____ K _____L ____ M .........
.
.
.
5
6 ...... One ___(Blank) _Two _(Blank) __Three ___One __Two .....
7
New List:
___A _____B ______C ______D ...........
1 One ___ Two ____Three __Blank
2
I have close to a 100X100 table (very rough estimation) and viewing different parts of the spreadsheet can require a lot of scrolling.
I want to create a drop down list based on the entries of Row 6 (starting from Column G). The problem is that when I do this, the drop down lists all the columns that have blanks and duplicate entries. I want a way to remove the duplicates from the drop down list. From what I have gathered it is not possible to simply just say "remove duplicates" to a drop down list, so I have to first create a second list that has everything without duplicates.
My thought is to use the Advanced Filter (Using Excel 2007) and paste a second, unique list in Sheet2. Unfortunately, I can't figure out what I need to enter into the different text fields in order to do this.
Once I have a unique list, I plan on making the drop down based off of that list. I want the unique list to include 1 blank cell as well to choose from (weird I know, but necessary because if nothing is entered, I have a Macro that will display everything but if there is an entry it will only display the data that is equal to the row 6 entry) Essentially, I want this Macro to have some kind of data security to it by only allowing the user to choose from the available entries in Row 6 after Column G.
Another capability I was hoping to have was that the drop down by dynamic. I.E. - if I add a new entry to Row 6 after Column G, it will add it to the unique list and thus the drop down without having to re-do everything.
Let me know if I need to reword / clarify anything to make more sense. I appreciate any help the community could come with.
Dan
(the second list could be listed vertically instead of horizontally if that is any easier)
Sample:
Current:
A . . . . G _____H ____ I ____ J _____ K _____L ____ M .........
.
.
.
5
6 ...... One ___(Blank) _Two _(Blank) __Three ___One __Two .....
7
New List:
___A _____B ______C ______D ...........
1 One ___ Two ____Three __Blank
2
Last edited:
