Disclaimer: not particularly a power user. Problem also probably more of a UI/UX issue than a technical issue.
From time to time when I'm working in excel I'll find I've derived an answer from a couple adjacent input cells and several intermediate calculation step cells below. As a contrived example, lets say I have pennies per number of hours and I want to convert to dollars per year. I'll start with hours per year (365.25*24) and divide that by the input "number of hours". Then I'll divide the input "number of pennies" by 100 to get dollars. Then I'll multiply the two together to get my output.
Without fail after working through a problem like this I will want to make a 2-D grid with an input variable on each axis and have the results filled into the grid. Unfortunately because I just did it using a bunch of intermediate cells I can't copy and paste, drag and fill, or any of the other intuitive mechanisms. To date the only solution I've found to this problem is to re-do all of my work in VBA as a single function and use =myFn(colval,rowval) in each cell.
There are a couple ways this could play out. One of which would be to call subordinate sheets (or chunks of sheets) as functions. This is how I've envisioned it in my head. It would be a terrible pain to make that work, and I'd be afraid it would confuse people who wouldn't understand which cells worked normally and which cells were function components.
Another solution would be to select an output cell and have it refactor it to a function that could be called. This is probably a lot easier to do, but might not be as maintainable by the kinds of people that don't understand VBA -- they could keep the "source code" cells around and recompile when changes are made, but all of the standard code generator / manual edit problems apply.
From time to time when I'm working in excel I'll find I've derived an answer from a couple adjacent input cells and several intermediate calculation step cells below. As a contrived example, lets say I have pennies per number of hours and I want to convert to dollars per year. I'll start with hours per year (365.25*24) and divide that by the input "number of hours". Then I'll divide the input "number of pennies" by 100 to get dollars. Then I'll multiply the two together to get my output.
Without fail after working through a problem like this I will want to make a 2-D grid with an input variable on each axis and have the results filled into the grid. Unfortunately because I just did it using a bunch of intermediate cells I can't copy and paste, drag and fill, or any of the other intuitive mechanisms. To date the only solution I've found to this problem is to re-do all of my work in VBA as a single function and use =myFn(colval,rowval) in each cell.
There are a couple ways this could play out. One of which would be to call subordinate sheets (or chunks of sheets) as functions. This is how I've envisioned it in my head. It would be a terrible pain to make that work, and I'd be afraid it would confuse people who wouldn't understand which cells worked normally and which cells were function components.
Another solution would be to select an output cell and have it refactor it to a function that could be called. This is probably a lot easier to do, but might not be as maintainable by the kinds of people that don't understand VBA -- they could keep the "source code" cells around and recompile when changes are made, but all of the standard code generator / manual edit problems apply.