Coming from Excel: How are there values in sheet cells that clearly have formulas behind them but they only show as a number?

Google Sheets
1

What magic is this? For instance on the balance sheet, it will show your accounts, last update, and amount.
When I click on the account cell , it clearly is a formula (as expected, although very complex) and brings the values over from the accounts page. I get that.

But when I click in the “Updated” or the amount, it shows as the amount IN that account, but there is no formula behind it! I understand that it is probably getting these from the hidden balance history sheet, but there is no formula.

How does this work?

I need to create a new page that brings in my accounts and the values, basically the same as the balance sheet. I am making my own investment tracker since the ones in here are overly complex and do not work well for laymen.

2

In both Sheets and Excel there are formulas that ‘spill’ data across a range of cells. A function like ‘FILTER’ is often used to gather data that meets a criteria, and then starting from the cell the formula is in, it fills that data in (usually vertically). That’s how you’ll see dynamic data with not formula behind it.

1 Like
3

Google Sheets has a cool Query formula that might help you for this case.

Here’s one I have modified from the community to make a table from the Accounts sheet:

=QUERY({INDIRECT("'Accounts'!$L$2:$L"),INDIRECT("'Accounts'!$J$2:$J"),
ARRAYFORMULA(IF(INDIRECT("'Accounts'!$O$2:$O")="Liability", -INDIRECT("'Accounts'!$I$2:$I"), INDIRECT("'Accounts'!$I$2:$I"))), 
ARRAYFORMULA(IF(ISBLANK(INDIRECT("'Accounts'!$J$2:$J")),"", TODAY()-INDIRECT("'Accounts'!$H$2:$H"))),
ARRAYFORMULA(IF(ISBLANK(INDIRECT("'Accounts'!$J$2:$J")),"", LEFT(INDIRECT("'Accounts'!$G$2:$G"),6))),
INDIRECT("'Accounts'!$O$2:$O"),INDIRECT("'Accounts'!$Q$2:$Q")},
"SELECT Col1, Col2, Col3, Col4
 WHERE Col1 IS NOT NULL AND Col7 <> 'Hide'
 ORDER BY Col6, Col1, Col2
 LABEL Col1 'Institution', Col2 'Account', Col3 'Last Balance', Col4 'Last Update'
",0)

The query formula resides in the upper-left corner of the table, and the rest of the table is magically filled with the data :slight_smile:

Or, maybe something like this to only include certain Accounts that are listed in another column. Change A3:A to your location.

=QUERY({INDIRECT("'Accounts'!$L$2:$L"),INDIRECT("'Accounts'!$J$2:$J"),
ARRAYFORMULA(IF(INDIRECT("'Accounts'!$O$2:$O")="Liability", -INDIRECT("'Accounts'!$I$2:$I"), INDIRECT("'Accounts'!$I$2:$I"))), 
ARRAYFORMULA(IF(ISBLANK(INDIRECT("'Accounts'!$J$2:$J")),"", TODAY()-INDIRECT("'Accounts'!$H$2:$H"))),
ARRAYFORMULA(IF(ISBLANK(INDIRECT("'Accounts'!$J$2:$J")),"", LEFT(INDIRECT("'Accounts'!$G$2:$G"),6))),
INDIRECT("'Accounts'!$O$2:$O"),INDIRECT("'Accounts'!$Q$2:$Q")},
"SELECT Col1, Col2, Col3, Col4
 WHERE Col1 IS NOT NULL AND Col7 <> 'Hide' AND Col2 matches '" &TEXTJOIN("|",true,A3:A)& "'" & "
 ORDER BY Col6, Col1, Col2
 LABEL Col1 'Institution', Col2 'Account', Col3 'Last Balance', Col4 'Last Update'
",0)
4

Sorry I am not clear what either of you are talking about. How do i know where it is “spilling” from if clicking in or around the surrounding cells does tell me anything about how its contents are generated?

I am not doubting you, just that in my 25+ years of using excel have never seen a cell that autopopulates w/o a “source”

TO make it more confusing the formula next to it does not contain the Cell (D10) in question at all anyway. My issue besides wanting to copy this formula into another sheet is if i were to accidentally type in it it messes things up and I get a REF error.

Screenshot shows what i mean. Cell just has the value, not the source where this value comes from.
IN excel I could potentially use formula> trace precedents but sheets does not seem to have this.

image

5

It’s coming from the arrayformula in cell B10.

Excel also does spilling, it just spills a formula instead of the data, and so there’s a similar “where is the source formula” question.

My Query formula suggestion was an attempt to give you a solution, which would make crafting your own formula unnecessary (and hopefully easier to modify to your needs). Try copying my first Query suggestion into your sheet to see how it looks.

1 Like
6

Wow, interesting and impressive indeed. I never knew of this ability. Anyway, I was able top copy your formula and it worked!

Going to work with this so thanks so much, especially since you were able to bring in the institution next to the amounts which is better and more informative than the Tiller info.

1 Like