2

I've got this sheet where I do some basic accounting, and I want to be able to input the balance in my pocket every once in a while. The idea is that the expenses I record until the next balance will be subtracting from the latest amount recorded (through a form).

In order to achieve this, I'm attempting the following line:

=index(FILTER(J:J,not(ISBLANK(J:J))),rows(filter(J:J,not(ISBLANK(J:J)))),1)-dsum('Respostes hipotetiques'!$A:$E,"Quantitat",{"Cash?","Data de registre";"",">index(FILTER(i:i,not(ISBLANK(i:i))),rows(filter(i:i,not(ISBLANK(i:i)))),1)"})

J:J contains the list of balances, and that part works. The problem lies in the dsum statement.

"Respostes hipotetiques" is a sheet that holds some fake data, and the amount is recorded in Quantitat. Cash? is either blank or "n" for not cash (I don't want to take a bank transaction into account to update my pocket money), and the "Data de registre" holds the automatic timestamp from the input form for each record of expense.

Lastly, I:I is where the timestamp is held for the fake data.

I've been experimenting with several combinations, and it seems I can't make any operations with cell references in the dsum criteria. If this is so, is there any way to achieve the intent of what I wrote?

EDIT 12/05/2015

Just to clarify, a verbose description of the thought process the formula is trying to capture is this:

If I had 20 euros/dollars/whatever yesterday at 3pm, subtract only those cash transactions that occurred after yesterday at 3pm.

2

You can refer to cells in the DSUM criterion. The problem with your query that you put a function inside of a string instead of appending its result to a string. That is, instead of

">index(FILTER(i:i,not(ISBLANK(i:i))),rows(filter(i:i,not(ISBLANK(i:i)))),1)" 

you need

">"&index(FILTER(i:i,not(ISBLANK(i:i))),rows(filter(i:i,not(ISBLANK(i:i)))),1)   

One can also use CONCAT or CONCATENATE for building query strings.


Since your formula is a bit convoluted, I illustrate the above with a simpler example. The Total table of the column below calculates the total population of cities founded after the date specified in the Cutoff column. The formula for the Total is

=DSUM(A$1:B$4,"Population",{"Founded";">"&C2})

+---------+------------+--------+-------+
| Founded | Population | Cutoff | Total |
+---------+------------+--------+-------+
|    1234 |        200 |   1333 |   153 |
|    1568 |        130 |   1111 |   353 |
|    1890 |         23 |        |       |
+---------+------------+--------+-------+
  • I see what you mean, and I think there is a confusion, maybe from self-teaching myself all of this. I don't try to concatenate, but to execute the comparison. I'm trying to compare the date of the transaction to the date of the latest entry on the current balance, and take into account only those that happened after the date). Basically, if I had 20 euros/dollars/whatever yesterday at 3pm, subtract only those cash transactions that occurred after yesterday at 3pm. Sorry for not being clear on this. – Analog Eye May 11 '15 at 22:31
  • This is exactly how I understood you: you are trying to execute comparison with the value of some cell. And I explained that to do this, you need a string with this comparison. So, if you want to check that something is > B2, you need the concatenation ">"&B2. – user79865 May 11 '15 at 22:37
  • Oh, I see. I just didn't realize the comparison needed to happen as a concatenation... Thanks! – Analog Eye May 11 '15 at 22:39
  • Just tested, and it works. Thank you! – Analog Eye May 11 '15 at 22:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.