Create a new worksheet based on values of a column

JohnBaron wrote:

Hi SGIII,

And: If I've seen a client 3 times, use categories to copy-paste on a different table and add notes there,

No, no! You would add the notes in the original table. Each time you see a client you add a row there with a new date. Categories will then take care of organizing it by client for you (and can also automatically give you a subtotal of how many times you have seen each client, or how much time you spend with each client; no formulas required!). You can even leave the table organized by client and just add a new entry under the appropriate client. Numbers will automatically fill in the client name for you. Then whenever you you need a chronological record of all clients, just turn off Categories and sort your table by Date Ascending. Much easier than all those columns and formulas!

The Categories feature is very powerful and flexible. But there are simpler database front-end apps like Access for the Mac (I happen to like Access!). You can check out Tap Forms. Or perhaps Ninox. But you may find that the Categories feature is all you need.

SG

JohnBaron wrote:

I know I can use categories, but I want to keep the original spreadsheet with the dates for an chronological overview.

Why NOT use Categories? You can group by name sorted by date, copy-paste results into other tables if desired, then simply toggle Categories off with the "switch" in the panel at the right to get back to your original table, which you can resort by date if needed..

That can save you a LOT of work and fancy formulas and extra index columns and things like that. (The other thread to which you link predates the return of Categories to Numbers. With Categories problems like these are much easier to address.).

SG

It does appear that you are thinking about this like a database. A spreadsheet is like the underlying table(s) in a database. We are forced to enter the data directly into the row(s) of the correct table(s). We can't enter it somewhere else and have it automatically added to the correct table.

My first post was a way to make a database report for a specific person. You would type a name at the top and it would use formulas to pull in the session data for that person from your other table. Nothing else you enter on that sheet/table would be associated in the "database" with the person or with any particular session. If you change the name at the top, the new person's session data will get pulled in but anything else you typed for the first person will still be there. Or if the order of data changes in the main table and the sessions get listed in a different order, anything you typed on a row for a particular session may now be on a row for a different session. This is not good data integrity.

For data integrity, anything associated with a particular session needs to be entered in the table that has the sessions, on the same row as that session. That is how a database would store it, as a field in the record. And general notes need to be associated with the person's name. They could be in another table in the "database" or you could have mixed data, putting these notes in the same table as the sessions. Or you can set up separate sheets for each client that have their notes and a table that pulls in the session data.

If your client base is small, you could use one sheet per client. Each sheet would have a place to type the person's name (which you will never change), a locked table that pulls in all their session information using formulas (locked so no one enters any data in it), and another table or text box in which to enter general notes. Session data will be entered in the main sessions table, not here. General notes can be entered here in the other table or in the text box.

If your client base is larger, individual sheets get hard to navigate (and/or you'll run out of them). You could put general notes in the main table on new rows. Each row would have the person's name and the notes would be typed into same column you would use for the notes for a particular session. I think it will be possible to separate them from the sessions for any report you want generated. You could definitely separate them with categories or filters to view them and enter more information. All the data will be in one table, all sortable by name or date. It will be a database of mixed data but so what.

JohnBaron wrote:

Maybe I should check Acces for Mac,

There is no Access for the Mac. But if, despite the powerful Categories feature in Numbers, you find yourself needing more of a database then be sure to have a look at apps like Tap Forms and Ninox. And there is FileMaker of course, with more of a learning curve and a steeper price. Not sure how good the database module in the free LibreOffice is these days. When I last looked at it several years ago, it was a little rough around the edges.

SG

It sounds like you want to create a table that contains only one name with all the dates that person was seen. Is that correct, that there will be only one person per table? I also assume you will type the person's name into a cell in that table so it knows for whom you want the data. This can easily be done but I want to be sure I got the question correct first.

I do have a concern about data integrity in this set of tables the way you plan to use them. The full list of names and dates is in one table. The second table will find the dates for a particular person and will list them in the order they occur in the first table. You will be adding notes on the second table next to each date for that person. But if you ever sort or rearrange the first table or add a session in the middle (say you missed entering one and put it in later), the order may change for that person. If that happens, the second table may list the sessions in a different order and your notes may not match up with the correct dates. Something to think about.

You may want to do all your data entry in the first table and use the other tables as "reports".

"Worksheet" is an Excel thing. Numbers has "sheets" that are like a blank canvas. On a sheet you can place "tables", shapes, text boxes, etc. A table is the closest equivalent to an Excel worksheet. It is the thing with rows and columns of cells.

HI John,

Badunit's suggestion is leading to a Numbers solution that is almost identical to what you've described for Excel.

Sheet 1 would contain the master table, containing the data for all clients and their visits, including your notes on each session.

Sheet 2 would contain a single table (to start with) set out as Badunit described to lookup and return all of the data from visits of a single client, whse name is entered ONCE in a specific cell of this table.

With those two tables created and the formulas entered into the second, you could simply enter a client name in the specified cell, and all records on the first table for visits by that client would be immediately copied to a list in the second table.

Removing the name from the specific cell would immediately remove all of that client's data from that table (with no effect on the first table). Entering the name of a different client into the specific cell, replacing the existing name would remove the existing data and place the data for the new client.

Making duplicates of the second table will produce new independent copies of that table. These can be places on the same Sheet or on different sheets. Like the first copy, each of these tables will display data for the client names in the specific cell on that copy of the second table.

Here's a tiny example:

Main Table and one Client Table:

Same two tables, with name changed in Client 1::B1

If a single table with one entry needed to display the data for any one of your clients won't fit your needs, the second table (Clients 1) could be duplicated as many times as needed to supply a copy for each of your clients, or for as many as you need instant acess to at any one time.

Entering the name of a client in the designated specific cell would then populate that table with the data for the client named on that table.

Example with three copies of the client table:

The copied Tables could be placed on separate sheets or on a single sheet.

Each of these would require client session notes to be recorded on the Main table, rather than on the Client tables which contain no entered data (other than the name of the client in B1.

Each of the Client tables contains the formula below, entered in Cell A2 and filled down and right to cell to the last cell in Column F. Additional copies, as needed, should be automatically added as new rows are added to accommodate data from more sessions.

Column G of the Main table is an index column providing search values for the MATCH function in the formula on the Client tables. The values are concatenations of the name i column B and the number of times that name has occurred from row 2 to 'this row' of column B.

In Main::G2:

IF(B2="","",B2&COUNTIF(B$1:B2,B2))

Fill down to end of column G.

Regards,

Barry