This is a question directly based on a formatted array table in Excel. ie a database sheet
I can lock my formula cells to prevent tampering with the formula however when the range is protected it is not possible to add a records and in fact my Excel is telling me it is impossible to do this with a protected sheet. Problem is my formula are vulnerable.
I've scoured the net, I've gone into custom edit in the revision tabs and nothing is overcoming the problem.
Does anyone know if this really is impossible as Excel is telling me or has someone else had this problem and can it be overcome with some VBA script?
I'm not going to split my table to separate the raw from the calculated as it is rather handy in the same place. The only other solution would be possible to force the data entry through a form which automatically blanks off the calculated cells.
I need to get beyond standard deviations as probably the limit in my terms right now, so I have resorted to an ancient version of SPSS on my laptop two back. Slow, but will get me there. Too many database softwares really don't do a lot of analysis but are brilliant and highly sophisticated calculators in effect. C'est ça!
Yes I am using pivot tables. They’re brill aren’t they?
Hi Nick, are you using Excel's pivot table function. This allows you to pull data from excel spreadsheets or from databases, such as Access or mySQL and present it in any way you wish to look at it. Great for slicing and dicing data as you can look at it from different views and at different levels. It would also allow you to ensure that the data is protected.
I always look at Excel from the point of view of: if I can go and get the data from a set of records (for example how much plasterboard did we use on floor 6 of the hotel, and then I need to know how much was budgetted for for floor 6 and in which room did we go over budget (not that I ever go over budget being a very professional QS)), then I can tell Excel to make the same decisions as me to get the info.
Now you are talking - my data sets are not enormous and normally once analysis is completed I tend to keep what is needed for the report, copy it on to a Word file and lose the Excel formatting. Once the report is submitted and considered complete and approved, I usually kill the Excel files anyway. Access and the hybrid idea seem to be on the way and especially given I am using Excel, which is a calculation programme as you say and basically for bookkeeping type work rather than a database. Mind you, it is not really a good economy to buy a database software because for the bit of work we do where either of us needs it... I used to use dBase, but once I no longer had free access to the software let it slowly die.
Now that sounds like my solution. Thanks, I'll give it a go tomorrow. Brilliant
Access (and correct me if I'm wrong) is a database programme whereas Excel is fundamentally a calculation programme.
Access has been developed to arrange, link and organise sets of unique records and present them through a series of user defined presentation options. I always draw the analogy that Access is to Excel what Publisher is to Word.
Problem is that Access although very flexible on output, is very cumbersome and you very soon find yourself with heavy megagig documents.
I don't use Access, so keep going. Brian sounds close but not there. I have, kind of, tried that. So say a little more about Access Nick, please.
and have the formula cell look the formula up. It sounds like a solution, but I'd still have to have a way in the data entry table to stop anyone direct typing in the calculated cell. and as soon as I do that Excel tells me I can't add records. I might try converting it to Access see if that gives me any better results: problem is I need to float it office wide and I'm probably the only one there who has any real clue as to how to structure and relate tables. None of the others use Access.
Pamela, thank you so much for your input. I really do appreciate you having taken the time to look out the video for me.
I also read down the comments and I'm having the same problem as the person with the first comment. As it is an array table, the problem of adding new records still exists when any part of the sheet is protected. (This does not happen with an unstructured table).
My other theory of forcing a form using the wizard came up blank too as I have too many fields for the form to handle.
thanks though.
Hi Nick, I found this video which seems to answer your question. Hopefully it's what you are after: http://www.youtube.com/watch?v=Fj9VesNjZwY
Yes, agree, but what I fear is several hours inputting and then losing the bl**dy lot to begin with. That would be typically when breaking the data down into different sets, especially where raw are retained as well as processed. I haven't thought about it until your question came up but thinking about it think you are right.
I would have thought it is possible Brian as when I use a data entry form the calculated cells are protected.
No help at all, but I too would like to know. I have been continually using 'save' to make sure my data are not lost for the same reason. Thought it was me. All useful answers get my gratitude too.