For the googlers looking for a solution : just scroll down to Data Validation! (i know there is a lot of stuff here that you couldnt care less about)
Ok so i have been using Excel alot lately, somehow i ended up with the responsibility for logging hardware at work and i inherited a rather simple excel sheet that i have been “improving”
Frankly i have learnt alot of really random useful things, i will quickly summarize now:
- The magical CNTRL-SHIFT-ENTER to do range commands like =if(A1:D29 = 42, “there is a 42 in that range”, “there is not a 42 in that range”)
- If you like doing stuff in excel I highly recommend doing the above as it can do really useful things including multiplying ranges by ranges like a matrix etc
- Tables and how they can be used in exel like =if(Table1= 42, “there is a 42 in that table”, “there is not a 42 in that table”) (again with the magical CNTRL-SHIFT-ENTER)
- A better alternative to VLookup http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#explain that allows you to return full records
- view->Freeze Panes to create split pages that are actually useful
- Random uses for Conditional Formatting (like turning every even column green and every odd column lighter green to make data more readable horizontally)
- Drop downs with data validation
- IFERROR so that i can replace scary messages like #VALUE! with “It appears you have entered this incorrectly”.
- =IFERROR(A9 * 5, “I believe A9 is not a number, dear sir).
- Obviously the above is a terrible example but its most useful on particularly on complex formulas…
- loads more that i have probably forgotten already
So you could say i’ve been having a bit of a crash course as i try and do more and more database stuff (some would say I should just use a database :p)
With the database stuff in mind I have been trying to assign each hardware item with a UID (Unique IDentifier), and use that to bring up other records…a very DB concept and trying to do it in excell had thrown up some interesting problems!
Creating the UID
Originally i used this formula: http://www.geekrant.org/2005/03/16/unique-references-in-excel/
and then copied and pasted the values..
however heres a little tip to save some time, just type something like
and drag that cell down, excel is clever enough to work out what you want to happen
but the above link did help me come up with a different formula for a linked ID.
Locking the UID…in a table
Ok this is where it gets tricky, there are 2 ways to do this, one is the way you will see everytime you google and the other is one you will catch every now and then, here they are with pros, cons and work arounds:
This is what i used in the end, because it allows you to sort the rows!
- Select the column
- Data -> Data Validation
- Allow: Custom
- Uncheck Ignore Blank
- Formula: REMOVED
- I would also protect the sheet but allow sorting / whatever else you want as it acts a work around for the con below
boom, now when ever you try and edit the cell it will throw an error and all the user can do is choose cancel
I also added an input message warning that it shouldnt be changed, this comes up as a little note underneath the cell, so its not as intrusive as a pop up
Sadly you cannot choose to not have the pop up, if you disable it, it disables the validation (which i think is daft)
- If someone selects a cell and then highlights the text to copy and paste it will bring up the error box, even though you havent changed anything
- Workaround: People can still right click and copy the cell and paste it somewhere else, if the page is protected i find this works fine and only pastes the value, sadly if its not protected it seems to paste the data validation with it
- It does not prevent someone right clicking the cell and choosing “clear contents”…again daft
- Worked for me
- Allows sorting of table cells
- does pretty much everything you want it to without the restrictions of a locked cell
- the little input note makes it clear to users why they cannot edit
Lock Cells and Protect Work sheet
This is easier but it seems to be more crippling when it comes to table stuff, is you can
- Right click -> Format Cells->Protection->tick locked
- go to Review -> Protect Sheet
- carefully revise the tick boxes you may want to disable some options
- Simple and relatively effective for most tasks
- Particularly good if you want to lock formulas
- if the cells are in a table, the user can no longer sort the rows (which is a little daft, it should be more flexible IMHO lock all, lock position, lock value, lock format etc)
Lock Unlocked Cells
If you want others to only view cells, but still sort them, you can do something else which is not lock any cells and under the tick boxes disallow users from selecting unlocked cells, this will stop them from editing anything.
- No Editing
So hopefully someone will find some of the above stuff useful, I must admit I do like excel, it offers alot of power, the problem is some of it is in obscure places (like cntrl+shift+enter).
I think as a programmer you have to sort of love and hate excel at the same time because you get to write really simply code for simply things but as things get more complex it always seems to be lacking, until you find that it does have the feature but they decided to hide it somewhere! ( i know the other solution is to crack open VB macros but i never like doing that for some reason, it always feels like a last resort)
As someone that likes Data and databases you also get the same sense of why did i not start this as a DB! and yet you know some of the stuff you have done would have been so much harder to do in a pure DB without resorting to a full blown C++ front end Database application….which to me always seems just around the corner, rather like updating this website!