Display queried values from different sheets and columns

I am quite new to Calc, so please be gentle. :wink:

To explain here a sample table of an imaginary hair salon entrepreneur. :slight_smile:

Customer Blonde    Brown    Black    Highlights    HairLength
-------------------------------------------------------------
Monica   Yes       No       No       Yes           15
Bianca   No        Yes      No       No            75
Lena     Yes       No       No       Yes           45
Selma    No        No       Yes      Yes           35
Kelly    No        No       Yes      No            45

I have several sheets like this in one spreadsheet (one sheet per salon). I now want a master sheet where I can display information from all Salons. For example:

  • all blonde customers
  • all blonde customers with highlights
  • all brown or black haired customers without highlights
  • all brown haired women with highlights and hair at least 35 long

These can be displayed in fixed columns on the master sheet, since I need the same information again and again.

Ideally I also have a selection list that has entries according to the table head ("Customer, “Blonde”, “Brown”…). On selection Calc should display the names of the customers as taken from across all sheets.

Things like this. You get the idea. I basically want to query the data on these sheets to get a statistical overview of the business.

Is this even doable or do I better start learning Base? :slight_smile:

You can do that kind of thing in calc using a filter. Data>Autofilter. After that you should see a little arrow button on top of each column. Clicking on that reveals a pull down menu where you can set filter criteria for each column. The result you would have to copy manually to your master sheet. This only makes sense if the amount of sheets and columns per sheet you have is manageable.

Another option would be creating a pivot table. I am not too familiar with pivot tables but here https://wiki.documentfoundation.org/images/4/49/CG3408-PivotTables.pdf you would find a starting point.

However, before starting learning how to create pivot tables I’d recommend a database system. The learning curves would be about the same. When starting that for a startup I’d recommend a MySQL database which you could manage with LO-base as front end initially but down the line you would need a more powerful database management front end. With LO-base as front end it is relatively easy to import data from a calc spreadsheet into a database table. To start with base this is a good read: https://wiki.documentfoundation.org/images/3/3c/BH5002-CreatingADatabase.pdf