Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
X002548
Not Just a Number
15586 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-22 : 13:45:07
|
| I don't think you can enforce it. You can add conditional formatting to highlight duplicates but there's nothing built-in to prevent dupes.You could try writing a macro that fires on cell update to look for dupes and then cancel the entry if it finds one.Can't put this into Access or SQL Server and just use Excel as a front? |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-09-22 : 14:02:22
|
quote:
Originally posted by robvolk
I don't think you can enforce it. You can add conditional formatting to highlight duplicates but there's nothing built-in to prevent dupes.You could try writing a macro that fires on cell update to look for dupes and then cancel the entry if it finds one.Can't put this into Access or SQL Server and just use Excel as a front?
I can do a lot of things..I'm contrained by software available to the users...and contrained by their IQI'm trying to collect dataBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspx |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-22 : 14:19:08
|
| If you're good with Excel macros you can fashion a data entry screen using spreadsheet cells, or you can design a VBA form with real controls. If you go the cells route you should lock and unlock the appropriate cells, then lock the worksheet to prevent unwanted edits. The macro could then use ADO to connect to the database and store/retrieve/update what they enter. I did this a couple of years ago for some people, took about 1/2 a day, and worked fairly well. You can use Excel data validation rules to make sure they don't enter garbage, and even process it further in the macro before submitting to a database. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2010-09-24 : 04:26:27
|
| a formula like countif($A:$A,A1) would tell user if there are duplicates. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-24 : 05:01:58
|
I use Conditional Formatting to highlight the cell red if it breaks constraints.What I tend to do is have a bunch of columns over to the right that contain the constarints. So AA1 might have IF(A1="","Cannot be blank",IF(A1="X","Cannot be X either...", "")) and conditional formatting on cell A1 that says IF(AA1="",0,1)and colour background to red.If a Cell goes Red the operator can scroll over the the right (i.e. AAn onwards) to see a message telling them what the problem isProbably an easier way, but that's the limit of my intelligence. Requires the user to copy the validation formuae downwards as they add more data though ...Where user must enter next available number I change the column heading from "ID" to something like="ID (" & MAX(A2:A65535)+1 & ")"so that the column heading shows the next larger INT that can be used - that will survive a sort of the data rows  |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Excel Uniqueness