Data types 2
I’m surprised that I seem to be the only one who often knows the data type I expect. Maybe I should do less ‘poor mans ETL’ type work?
Would it be useful then if cells could have a fixed data type? one that didn’t get overwritten by pasting (unlike the rather flimsy data validation)? one that was at the cell level, not in the cell entry event (unlike the flimsy data validation*)
*What I mean here is is if you have a formula in a cell (with data validation) that results in a valid value, everything is fine. If you then change one of the precedents of that formula to give a invalid result, the data validation does not trigger. - I’m not saying thats bad or wrong, what I am saying is something that was more robust be useful?
Also what do you think of being able to give cells a unit (like miles, km, hours or something) that could then be used to check you are not doing something dumb. (I’ve lost count of the number of times I’ve added pounds, dollars, yen, and euros or whatever - luckily having the yen in there make that blunder obvious). I’ve heard this units suggestion a few times, I’m not sure how much use I would make of it, what about you?
cheers
Simon
Thursday, 8th May, 2008 at 12:46 am
Whenever I use data validation I try to keep it out of formula cells. Now that I’ve said that, it’s also not realistic to think that I try to guess what every user is going to do with the file and where they might be placing their formulas.
Thursday, 8th May, 2008 at 5:49 pm
I like the concept of units - and would use them if they were available - I guess it would be good for a user to override them in some way (possibly with a visible indicator) if they really needed to get around a limitation induced by the units. It would also make spreadsheets stronger from a audit/review perspective, which would be important for critical spreadsheets.
–charlie
Tuesday, 13th May, 2008 at 3:58 pm
Actually, the way I read the responses was that most of us know what type we expect - but we also know that users often produce the unexpected so have another line of defence in VBA (or C# or whatever).
Agree with trying to keep validation out of formula cells. Ambivalent about units.
AJG
PS I’m also a big fan of reading in data from wksheets and faking arrays for speed purposes - clients frequently insist on data being held in XL rather than a dB so you’re stuck trying to make it as fast as possible with what you’ve got.