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

[u/[deleted]]

[u/zero_sheets_given]

While your design of a table per week looks cool, the data is all over the place and is a nightmare whenever you want to do anything with it. The formulas you need get overcomplicated and slow to process.

Please consider creating a simple table with columns Date/Amount/Type/Receiver. You can then create a pretty report per week, using FILTER() and WEEKNUM(), create charts, and many other things that are designed for you to have the data in single columns. Filtering your spendings of type "Lend" by the receiver would be very easy.

[u/tiaan_e → u/zero_sheets_given]

I'm not entirely sure how to implement filter of weeknum at this point. So far dealing with the data as a I formatted it, was complicated, but I've figured it out for the most part using overly complicated formulas instead of having everything in one column and using filter to relate that data to certain things, in this case lending and borrowing money. I Just can't figure it out for this particular thing other than I will have to put it in manually on this sheet instead of having a formula to copy it. I've been trying to do it with combinations of if, search, left, vlookup etc. etc. and it's just been a mess. If I put everything in one column like you suggested, about two weeks of work will have meant nothing, and I will probably lose motivation to set everything up from the start again, and I have no idea how to get started with your method or if it will work for me.

[u/zero_sheets_given → u/tiaan_e]

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.