The command offset does what you want: =offset(D3, -1, -2) is the value 1 row above and two columns to the left of D3. So, your formula for D3 could be
=offset(D3,-1,0) + offset(D3,0,-2) - offset(D3,0,-1)
Extended down the column, it will do the intended computations regardless of row insertions.
When stacking arrays vertically, they must have the same number of columns. In case of an error, query outputs a single cell with #REF or #N/A or another message. You put iferror wrapper around it, but that only makes it so the output is one empty cell. Problem is, it is one cell and you need 8 columns to match the other array.
Solution: add a blank row with 8 cells as the second argument of iferror, to be used in case of errors.
=SORT(ArrayFormula({
IFERROR(QUERY('5 Star Clubs (Hidden)'!A6:Z,
"select A, B, C, D, E, F, H, I
where A != ' ' AND F IS NOT NULL"),
{"","","","","","","",""});
IFERROR(QUERY('5 Star Gear Clubs (Hidden)'!A6:Z,
"select A, B, C, D, E, G, H, I
where A != ' ' AND F IS NULL"),
{"","","","","","","",""})
}),7,FALSE)
Specifically, the error occurs because there is no sheet named '5 Star Gear Clubs (Hidden)'. Suggestion: when a formula throws an error, enter its parts in separate cells (without iferror wrappers) to see what they do.
Best Answer
INDIRECTand try like this: