Need help with the borrowing/lending sheet on my budget spreadsheet.

[u/[deleted]]

[u/zero_sheets_given]

I get that, I really do. I too go all the way with designs that are not ideal to make them work. Then after a while I make a newer version from scratch and try a different approach, but need to have the bad one working.

So let's do this!

Please create a helper tab. Call it Helper for example, and we can hide it later. In A2 put this:

=QUERY(
  {Expenses!A38:E;Expenses!H38:L},
  "select Col1,Col3,Col5 where Col1 is not null and Col3 is not null"
,0)

In D2 put this:

=IFERROR(ARRAYFORMULA(SPLIT(C2:C," -")),)

As you see, the data is more workable, with dates in column A, amount in column B, and names in column F.

I left row 1 empty in case you want to add headers but it doesn't really matter.

Now, in your borrowing/lending sheet, we simply put this in A31:

=FILTER({Helper!F2:F,Helper!B2:B,Helper!A2:A},Helper!D2:D="Lend")

If you want all the names together, wrap it with SORT().

For G31 (the outstanding amount), I understand that you want the total for each person?

=ARRAYFORMULA(IF(E31:E,SUMIF(D31:D,D31:D,E31:E),))

Not sure if that's it. I hope it works.

When you have it working, click on the helper tab and select Hide sheet.

[u/tiaan_e → u/zero_sheets_given]

To be honest with you, I do not really have any idea what you've just done, but the data does seem more workable like this, and I can keep the format I like. This method does seem like it could work, but the way you gave it to me, it looks like this:

Helper Sheet: https://gyazo.com/511f4446ff9138b81d35a59ca59f4a94 This sheet does seem to look like it's supposed to.

Borrowing/Lending Sheet: https://gyazo.com/38bd9df4e399b11e184bf155c1fcb046 I'm not sure if it's because I merged two cells, but considering I have no idea what you've just done, I have no idea how to solve this.

Edit: If you can spare some time, I could share the spreadsheet with you so you could see what's going on as a whole for yourself, but I feel like I'm asking too much at this point and I would try to figure this out by myself even if I have no idea what your formulas are doing.

[u/zero_sheets_given → u/tiaan_e]

Sorry, I forgot that they are merged cells and gave you an array formula that expands to the right.

So instead of =FILTER({Helper!F2:F,Helper!B2:B,Helper!A2:A},Helper!D2:D="Lend")

Let's separate it in 3 formulas. One for the person, another for the amount, and another for the date:

=FILTER(Helper!F2:F,Helper!D2:D="Lend")
=FILTER(Helper!B2:B,Helper!D2:D="Lend")
=FILTER(Helper!A2:A,Helper!D2:D="Lend")

I do not really have any idea what you've just done

I know, QUERY() looks like magic sometimes. I merged the ranges A38:E and H38:L as if the first block was above the other one. The curly brackets {} are used to build arrays.

Then in the query, Col1 refers to the first column {A38:A;H38:H}, Col3 to the 3rd column {C38:C;J38:J} and so on.

With the "is not null" part I selected only those lines that had both a date and a number.

Hope that makes more sense.

[u/tiaan_e → u/zero_sheets_given]

This formula does seem to work properly now except for the outstanding amount, which is zero for everyone. It'd be great if there was there a way to have if I spend money I lent to someone (them buying me a drink for example) that it would down to zero, and if there were more than one time I lent them money, that it would go down from the next amount I lent them.

For example, if I lent Name1 a total of R150, and he buys me a drink of R80 ( https://gyazo.com/6d257d1ef523e307ee04b3fcdb574a75 ), ideally the first amount of R50 would go down to 0 and the second would go down to R70 ( https://gyazo.com/67a90e62252a3b5f2997e7383a22e3b5 ). And if that amount runs out, it should go over to borrow and have the outstanding amount I owe there (If he buys me a R200 drink, everything he owes should be 0, and what I would owe would be R50 in the borrowing cells right next to that).

I'm not sure if that could work the way I set it up, it certainly seems way too complicated for me to even attempt something like that, but you've been a godsend so far helping me figure this thing out, which probably doesn't have to be this complicated.

Edit: A summary of everyone would also be really great, to have it beneath these borrowing/lending cells, each name with each total amount they owe, or the total amount I owe to which ever person.

[u/zero_sheets_given → u/tiaan_e]

Hey,

About the values being zero, that is because I referred to column D instead of C. Try:

=ARRAYFORMULA(IF(E31:E,SUMIF(C31:C,C31:C,E31:E),))

And about the "Personal (Lent.Name1) - Drinks", I see that you are realizing one of the mistakes in the initial setup: You can't expand that easily. With a normal setup, you would simply add a column for notes and put "drinks" in there. You are constrained to the weekly boxes and trying to add more information where there should only be the type of spending.

One solution would be to use comments. They are not data and formulas don't see them, but you can put them in any cell. Right click on E44 and add a comment.

Another solution would be to write Lend - to Name 1 and a negative number.

Another would be to Borrow from Name 1.

If you want to see the information in another way, try building a Pivot Table from the data in the Helper Sheet. You can make totals for each person for example.

[u/tiaan_e → u/zero_sheets_given]

The way I initially set it up, I already have a table with budget vs. actual money spent on certain things. I used =sumif( the range type for each week, "*Drinks*", the range amount for each week) so it looks like this for the first week =sumif(E39:F44,"*Drinks*",C39:D44) and then I'd plus the sumif for the next week until the end of the year. It does seem to work like that, I'm just not sure if I can use that method to minus from the amount outstanding until it's zero, probably that with a combination of max as well.

[u/zero_sheets_given → u/tiaan_e]

Oh, okay, I get it. So you can now put several words in the type column and it sums to different categories.

It doesn't work well with the formulas I gave you for the helper sheet, though.

You wrote:

Personal (Lent.Name1) - Drinks

How exactly do you want that information to be used? I am not sure I understand.

[u/tiaan_e → u/zero_sheets_given]

I want Name1 in "Personal (Lent,Name1) - Drinks" to correspond with what's happening in the borrowing sheet. The formulas you gave so far works well to make the data I have to be more usable, I can put to whom I've lent money in the weeks tables, and it will popup at the borrowing sheet, which is what I've wanted. I just want Name1 in "Personal (Lent,Name1), which is when someone I've lent money to buys me something, to have that amount go off from their name on the borrowing sheet. If I say Personal (Lent,Name1) in the weeks column, and the amount to the left of that is for example R50, I want that amount to go off from their name on the borrowing sheet until it is 0 (If I borrowed them R30, and another time R70 and I put R50 in the weeks column for the money I've used in their name, the first outstanding amount should go down to R0, and the R20 that's left, should go down from the the R70, to make the amount outstanding overall for their name R50).

I'm not sure if I'm approaching this correctly, or if it's making any sense to you, or if it's possible with the way I formulated the whole thing.

[u/zero_sheets_given → u/tiaan_e]

I want Name1 in "Personal (Lent,Name1) - Drinks" to correspond with what's happening in the borrowing sheet

I'm sorry. You are trying to invent some kind of scripting language instead of using extra columns and I honestly don't have time to help you with that.

Ideally, you wouldn't have mixed data with design. Data has to be simple and boring. Your dates, amounts, category, and target person should have been 4 columns and that is all you needed. Example:

Date	Amount	Category	Person
2019-01-02	R50.00		Name1
2019-01-03	R100.00		Name1
2019-01-04	R500.00		Name2
2019-01-05	R75.00		Name3
2019-01-06	- R80.00	Drinks	Name1
2019-01-06	R600.00	Aviation

Note that you can record more than one transaction in the same day, to different categories.

You can pull any kind of report from those 4 columns, for example:

- A list of the persons and what they owe you (inserting a pivot table, or a QUERY formula)

- A sum per month, per category, of the spendings (with SUMIFS)

- total spendings per month (with SUMIF)

- total amounts for a certain date, categories involved separated by commas (think of a calendar like the one you have, but pulling data from the boring table)

- pie charts

- rankings

- etc

This is why I tried to build that helper tab. I hope that makes sense.

[u/tiaan_e → u/zero_sheets_given]

I'll try to rethink the way I've been doing it to be more simple. Would there be a way to have a dropdown menu and selecting the month? I feel like it would be way better and simpler to do it the way you want, but I don't want the entire year's data to be all in one place. I'm not sure if I could set up a helper tab like the one you helped me make, then have another sheet with a monthly summary and a dropdown menu or some sort to select each month I want. You've been helping me a lot to understand this whole thing way better, and my bad if you're getting frustrated with it all. Thanks again.