No duplicates in Named Range formula?
Hello
This formula works great for a Named Range getting the list of non-blank entries on the Doc Issue Log Sheet rows 5 down in Column D.
But now I need to ensure there are no duplicates in the list.
Please Login or Register to view this content.
Anyone see how to modify this formula?
I appreciate the help!
Respectfully,
Lost
Last edited by leaning; 07-11-2011 at 03:34 PM. Reason: Solved!!! YAYYY!!
Hmm?
Not sure that your formula removes blanks, if this gives the correct result with a list that includes blanks, it might well just be by coincidence.
See if this work book gives you some ideas, without a sample workbook it is difficult to see what you are really trying to do.
Names "Full_List"
Refers to:=
Then in E5Please Login or Register to view this content.
Drag/Fill Down to the end of Column DPlease Login or Register to view this content.
Then Data Validation in say G5
Data > Data Validation Allow:= List
Source:=
Please Login or Register to view this content.
Or Create a second name "No_Duplicates"
Refers to:=
Please Login or Register to view this content.
Then Data Validation in say I5
Data > Data Validation Allow:= List
Source:=
Please Login or Register to view this content.
Not sure if it will solve your particular problem but it might give you a start.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
Re: No duplicates in Named Range formula?
Marcol,
Workbook attached. The yellow range in the first sheet is used for the DV list on the second sheet. If you look at the dropdown on the second sheet, there are two dupe entries.
Both of your methods have me dragging down formulas to cells. With the named range formula I am using, the formula is only where you create the named range and there is no dragging down formulas into cells.
Do you see a way to modify the named range formula itself?
Lost
Hi,
So you want to prevent the user from typing a dup record? or You just dont want to see a dup record in the drop down list?
..
Thank You, Mike
Some Helpful Hints:
1. New members please read & follow the Forum Rules
2. Use Code Tags...Place[code]Before the first line and[/code] After the last line.
3. If you are pleased with a solution mark your post SOLVED.
4. Thank those who have help you by clicking the scales at the top right of the post.
Here...
realniceguy!
In the index (first sheet), duplicates are allowed.
It's just when the index values are used in a dropdown in later sheets that no duplicates are allowed.
HTH!
Lost
Progress!
1. Original list is in Column B.
2. No dupes list created in Column Q.
3. Data validation looks at Column Q.
Here is the DV formula that needs fixing to not allow blanks:
Here is the line from the code for no duplicates:Please Login or Register to view this content.
Please Login or Register to view this content.
Progress!
Lost
In the macromodule of the sheet 'Doc Issue Log'
Please Login or Register to view this content.
snb,
I like that. Doesn't even use a named range or create a no-dupes column. Really slick.
The first workbook I posted had some bad info.
The column I am using for the DV list is the filtered B column, not D.
I doctored you code:
1. What should c01 be Dim'ed as so that it can compile? As Range?Please Login or Register to view this content.
2. If I want to use that same DV list on 20 other sheets, do I just repeat the If Target.....c01 section over-and over with the new sheet name in place of "MTE Usage Log"?
3. If the user inserts a row and then leaves column B blank, the DV list shows up #N/A.
no need to dim:
to prevent N/A:Please Login or Register to view this content.
To put into cell B3 of 20 sheets:Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by snb; 07-08-2011 at 11:30 AM.
Try this workbook it is based on Excels' native List/Table (2003/2007)
Use the grouping buttons to show/hide the dynamic named lists used for validation lists, I've given examples for "Work/Service Order" & "Dwg No!
See this link for more info for using List/Tables
Overview of Excel tables
There are many advantages to using this feature, not the least are,
1/. Adding deleting rows automatically inserts/adjusts any formule, validation, etc in the columns.(use the Drag Handle bottom right in the table)
2/. Sub Totalling and other features (max, min, count, etc ... ) are easily achieved through the drop-downs in the Totals row.(The last row in this example)
3/. No VBa required.
In my opinion you will be well served in future if you study the above link and its' follow on links.
Hope this helps
Last edited by Marcol; 07-08-2011 at 11:21 AM.
Re: No duplicates in Named Range formula?
All,
Another fine job from the forum!
1. List on Sheet 1 is used without blanks and dupes as a Data Validation source.
2. No named ranges or helper columns. All done with VBA with essentially one line of code.
Sweet!
(Final Workbook attached. PW is "SECRET").
snb: FYI. the DV list is hardly ever used in the same cell in other sheets. So, I just repeat that code, but reference the sheet name and cell affected, right?
Thanks, everyone and have a great weekend!
Sincerely,
Lost
Marcol,
I will look at the info you posted tonight. Thanks!
Respectfully,
Lost
Re: No duplicates in Named Range formula?
snb,
Thanks for the help to apply your code to other sheets:
Good stuff! Reps and props!Please Login or Register to view this content.
Respectfully,
Lost
Last edited by leaning; 07-11-2011 at 03:31 PM. Reason: add indents
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks