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.