I have a formula on one sheet like the following:
$Key reference specific columns on another sheet in the same file.
This works (yay)
Unfortunately, I have to copy and edit the formula for each column (and across all rows, since each row has a different string at the end as part of the condition). I want to make it easier to copy/paste this formula across multiple columns on the first sheet such that I'd get something like:
column-A: =SUMIFS($Amount,$Date,">=01/01/2017",$Date,"<02/01/2017",$Key,"=IN:ALI*") column-B: =SUMIFS($Amount,$Date,">=02/01/2017",$Date,"<03/01/2017",$Key,"=IN:ALI*") ... column-K: =SUMIFS($Amount,$Date,">=11/01/2017",$Date,"<12/01/2017",$Key,"=IN:ALI*") ...
So, I added a row so that near the top of each column (below headers / before data) I entered dates, e.g.:
A3: 01/01/2017 B3: 02/01/2017 ...
The plan being to replace the hard-coded strings in my formula with cell refrences (e.g.: A$3, B$3, etc.), but I cannot seem to figure out a way to plug them into the formula in a manner that will allow them to update automatically/correctly when pasted into subsequent columns.
"<B$3" - but the 'A' and 'B' don't get updated when the formula is copied from cell
">=", A$3 ...
"<", B$3 - but I get a syntax error.
">=" A$3 ...
"<" B$3 - but that too gets a syntax error
Is there a way to do this?
(note: If I put all the formulas into a text-editor to do query-replaces on the date strings, I still have to click into each cell to paste them, otherwise the formulas end up as text-strings rather than formulas - if there's a good way around that I'd like to know too)
After various Q&A's and design morphing, this ended up as: AppleScript Numbers: Process data from Quicken