Hey All,
I used to use Google Sheets to do spending tracking and I’ve recently converted it to an ODS file. I have it working pretty much as it used to with one exception. On one of the sheets in the file I had a filter function that was able to pull in data from all the other sheets, but filter isn’t a formula that is supported in Calc, so I’m not sure how to proceed. The way that it works is that I have a drop down in one of the cells where I can pick the spending category, then the filter function would scan through the month sheets (1-12) and display any line where it had the same category as what I picked in the drop down. Here’s the formula from Google Sheets:
iferror(filter({‘1’!A:F;‘2’!A:F;‘3’!A:F;‘4’!A:F;‘5’!A:F;‘6’!A:F;‘7’!A:F;‘8’!A:F;‘9’!A:F;‘10’!A:F;‘11’!A:F;‘12’!A:F},{‘1’!B:B=$A$1;‘2’!B:B=$A$1;‘3’!B:B=$A$1;‘4’!B:B=$A$1;‘5’!B:B=$A$1;‘6’!B:B=$A$1;‘7’!B:B=$A$1;‘8’!B:B=$A$1;‘9’!B:B=$A$1;‘10’!B:B=$A$1;‘11’!B:"&"B=$A$1;‘12’!B:B=$A$1}),“n/a”)
On the filter sheet the drop down is in cell A1, then on each of the month sheets columns A through F contain the data for each spending thing (date, category, business, etc) and column B is where the category sits. Is there any way for me to replicate this in Calc? Any help is appreciated.