|
|
|
|
|
|
by LawTalkingGuy
1338 days ago
|
|
|
How do you use a spreadsheet in both an intuitive and a powerful manner at once? For instance, you intuitively start with just a column of numbers, and then when you forget what they are you move the column down one and put a title above it, etc. Working this way I often end up with formulas that are off by one or two cells even though things should be adjusted, and it's hard to audit without mousing over the formulas and just looking. If I'm trying to write a tool for others to use I'll do something more formal like putting the data on its own sheets and naming them. That helps a bit, but means that all my data is hidden on different sheets and I can't get a holistic view of the problem. And to be consistent in my formulas I'm tempted to use this model even for data that doesn't need it (not an array, for instance). And it still doesn't help for array-bounds type problems. |
|
|
I think everyone has slightly different workflows and this has no real solid answer. There’s always some trade offs. Just working in excel a lot helps, if you’re working on varying levels of complexity. I do it as a job and often helping others so I have a lot of exposure to different problems and input data types and even the desired outputs.
The intuitive example you mentioned never happens to me for example because of 2 things; 1) I intuitively leave space to add a header, it’s such a common thing to do, my intuition knows to account for it up front 2) if for some reason I ignored #1, I know how to move things without breaking the formula references and when to employ different approaches to that problem. Things like how copy and cut differ, Inserting a row above row 1, etc. One of my little hacks is avoiding referencing a range like A1:C5 and instead will make it A:C if it’s just a basic table of data. Your file may have a ton of references to this table of data once it’s built out and adding a row of data then requires some manual housekeeping which introduces an opportunity for a bug to occur. With my approach, I can add or subtract data or rows and non of the range references need to change. (Someone May point out that you could expand the table range for 6 rows in a way that the other references would expand, it’s true but I don’t design my files for that, because adding a row may have break something else to the right of column C).
A lot of the way i use and setup the data within a spreadsheet is in anticipation or avoidance of future issues. This only comes with experience. Which is kind of my answer to your more complex example. When developing for others, you’re usually trying to hide the data and complexity of things and expose only the useful bits for that end user. It’s not perfect and is very annoying that I have to hide and unhide things constantly to inspect the functionality. But that’s just the way it is. I’ve done some things like written little macros that hide all of the background stuff if a keyword is in the file name (imagine having a dev and prod version of the file, if prod is in the file name when saving all the cleanup and hiding of things happen. That’s what I distribute. The dev version is the same and where I work. When I save that file prod is not in the file name so everything in that macro doesn’t execute.)