- May 19, 2011
- 21,570
- 16,823
- 136
I had a late night last night (and not particularly great sleep to boot). As I don't have a set schedule this morning, along with the nature of being self-employed, I've been wondering how best to use this time. I decided that I would try and do some database dev work for a customer and if it feels too much then I'll focus on something else instead.
My wife is working from home this morning and has interrupted my flow of thought several times while I've been trying to get my sleepy brain on the productivity rails, and the latest set of queries have been basic spreadsheet queries, though the last of which being one that I thought I'd want to have a play with before I make my recommendation of how to implement what she wanted.
She had a bunch of cells in one worksheet that she wanted totalling up so I showed her how to do that, then the last query was that she wanted to copy that result and use it in other worksheet. As one of my workflows includes taking a calculated value and inserting it in another spreadsheet altogether and aside from linking multiple files together (something I really don't want to do), I've been reduced to noting the calculated figure from spreadsheet1 and typing it into spreadsheet2. Naturally my brain dwelt on that problem when thinking about her query.
From one worksheet to another is easier than one spreadsheet to another though, right? I could use absolute cell references surely! Ok, so I need to abcellref the worksheet name too, e.g.:
Hey, it works when I copy and paste that cell onto the second sheet! ... then it dawned on me. worksheet2 just needs to have =sheet1.calculatedcellref
<facepalm>
I suspect if I do dev work today, I'm going to come back and look at today's code another day and wonder wtf was I thinking.
Having said that, the overly convoluted solution has the merit that when one double-clicks on that cell to find out the cell range/formula, one gets straight to it rather than having to then double-click on the original calculated cell. But the simpler solution likely scales a lot better.
My wife is working from home this morning and has interrupted my flow of thought several times while I've been trying to get my sleepy brain on the productivity rails, and the latest set of queries have been basic spreadsheet queries, though the last of which being one that I thought I'd want to have a play with before I make my recommendation of how to implement what she wanted.
She had a bunch of cells in one worksheet that she wanted totalling up so I showed her how to do that, then the last query was that she wanted to copy that result and use it in other worksheet. As one of my workflows includes taking a calculated value and inserting it in another spreadsheet altogether and aside from linking multiple files together (something I really don't want to do), I've been reduced to noting the calculated figure from spreadsheet1 and typing it into spreadsheet2. Naturally my brain dwelt on that problem when thinking about her query.
From one worksheet to another is easier than one spreadsheet to another though, right? I could use absolute cell references surely! Ok, so I need to abcellref the worksheet name too, e.g.:
Code:
=SUM($Sheet1.$C$7:$Sheet1.$C$9)Hey, it works when I copy and paste that cell onto the second sheet! ... then it dawned on me. worksheet2 just needs to have =sheet1.calculatedcellref
<facepalm>
I suspect if I do dev work today, I'm going to come back and look at today's code another day and wonder wtf was I thinking.
Having said that, the overly convoluted solution has the merit that when one double-clicks on that cell to find out the cell range/formula, one gets straight to it rather than having to then double-click on the original calculated cell. But the simpler solution likely scales a lot better.
Facebook
Twitter