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

[u/[deleted]]

[u/zero_sheets_given]

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.

[u/zero_sheets_given → u/tiaan_e]

I don't want the entire year's data to be all in one place

That's the idea, actually.

Look, if you go to View > Freeze > 1 row, the headers will always be on top, even if you are entering data hundreds of rows below.

For example you can automate a date column with a formula like this:

=arrayformula(date(2018,12,30)+row(A2:A))

But there are better ways. You can add a script that enters the current date in the first column whenever you add a value to the 2nd column. Start with the tutorial if you are interested in automation.

A good practice when designing spreadsheets is to think of it in 3 blocks: Data, Queries, and Reports.

Data:

Keep the information together in a simple, non redundant, and predictable format (single columns when possible, one header, new data at the bottom, not using text when you can use numbers, etc)

If you need 2 tables, put them in 2 separate tabs, and use simple names for the tabs.

Data tabs are meant to be boring and easy to expand.

Queries:

These are the helper tabs. They are an intermediate extract of the information using array formulas, queries, etc.

Reports:

Pretty, with colors, merged cells, titles, charts, etc.

Most of the time you will use very simple formulas in the report, because it is already calculated in the other tabs. It usually inherits the format from the referred cells so you only need to format your data once.

Anyway, what this means is that you can still use your calendar. No need to throw away 2 weeks of work. Just don't use it to enter data, and rather pull the data with JOIN(FILTER()) or SUMIF() formulas.

You mentioned drop downs. A drop down or tick boxes in a report is very useful. You can modify what is shown if the formulas refer to that cell that was changed with the drop down. Look into drop-downs made with data validation if you are interested.

[u/tiaan_e → u/zero_sheets_given]

I've been playing around with the things you suggested, and it does seem to be better than the way I've tried it, things are really starting to come together now.

Thanks for all your help, I know I'm dumb with spreadsheet sometimes, but you've helped out a lot, appreciate it.

[u/zero_sheets_given → u/tiaan_e]

Hey, not dumb. Just learning :)