How to create categories of cells so I can reference to them in other sheet?

asked 2019-09-10 19:59:55 +0200

kiedis gravatar image


I am trying to create a monthly budget sheet. It consists of 2 sheets. The first sheet consists of 2 columns - column EXPENSE_TYPE and column AMOUNT. An example:

EXPENSE_TYPE | AMOUNT Travel 1007$ Food 300$ Utilities 54$

The second sheet is listing all the transactions that I made during particular month, so it has columns of DATE, EXPENSE_TYPE and AMOUNT. An example:

DATE | EXPENSE_TYPE | AMOUNT 20190901 Food 23$ 20190902 Utilities 15$ 20190903 Travel 315$ 20190903 Food 22$

My goal is to bind the EXPENSE_TYPE and AMOUNT columns in Sheet1 and Sheet2, so that when I fill in data in Sheet2, the AMOUNT column with appropriate EXPENSE_TYPE row in Sheet1 is automatically updated. In my given example above, you can see that in Sheet2 there are 2 rows of Food expenses. So the sum of these expenses (23$ + 22$ = 45$) should automatically be calculated and displayed in Sheet1 column AMOUNT and row Food.

How can I do it? And maybe you could tell how this thing that I want to implement is called officialy? Binding? Referencing? Something else?

Thank you

edit retag flag offensive close merge delete


Not sure I understand, but, in sheet1, on any cell, hit "=", then go to sheet2 and enter the cell you want to show up in sheet1 to update, and hit "Enter".

Mark McLean gravatar imageMark McLean ( 2019-09-11 12:08:31 +0200 )edit

just noticed that some parts of my question were not formatted in the way that I intended. However, what I want to do is to create drop down menu for some cells. If you ever saw Monthly budget template on Google Sheets, there is certain a column called Categori and all rows under that column have dropdown menu where you can choose the category of expense. How could I create a similar drop-down menu in Libre Calc?

kiedis gravatar imagekiedis ( 2019-09-11 21:59:57 +0200 )edit

OK, I actually managed to create drop-down manu via Data -> Validity. Now the only step left is to somehow bind the cells in different sheets so that when I make an entry in Sheet2, the total amount of expenses gets automatically updated in Sheet1. I tried to copy a formula from Google Sheets but it didn;t work:

=IF(ISBLANK(C6), "", SUMIF(sheet1!$c:$c,$C6,sheet2!$d:$d))

This formula is in Sheet1 AMOUNT column C6 and it tries to catch the data from Sheet2 column C (EXPENSE_TYPE) and column D (AMOUNT)

kiedis gravatar imagekiedis ( 2019-09-11 22:22:13 +0200 )edit