Copy cell format in a formula, Numbers

According to the list in column D of the table in your screenshot,Ian's suggestion of using conditional highlighting will work well, using a set of seven rules.

Each of the rules will have this structure:

Text ends with -f, -n

or

Text ends with m, -f, -n

Where m, f, and n are the (masculine), feminine and neuter forms for each type.

The masculine ending i optional as the feminine, neuter pair is distinct for each type, so the order of applying the rules is not significant.

Select all of the non-header cells in the column where the colours are to be applied, then create the rules.

Takes you to:

Where you enter the appropriate string containing the text starting with the first - and going to the end of the entry for that Type.

Choosing Custom Style opens the Colors panel, where you can set a custom fill colour, or use the dropper tool (lower left) to copy the colour from an existing cell.

(colours matching one of the listed samples can be set by choosing that 'color fill' in the menu.

A rule is needed for each type except the first. If the content of the cell does not end with one of the tested strings, no conditional highlighting will be applied.

Regards,

Barry

PS: that space in front of the feminine ending in Type 5 will prevent the conditional highlighting of that cell, as "- f…" is not a match to "_f…" Take care regarding typos!

B

Hi curlydog,

I see that my good friend Barry beat me to the reply. But here is something I prepared earlier, so here we go.

I see two separate aims:

• Aim 1 is to automatically apply a distinct colour fill to distinguish the adjective endings;
• Aim 2 is to sort tables according to their cell colour fill.

Aim 1: automatically apply a distinct colour fill to distinguish the adjective ending

As Barry said, use Conditional Highlighting with "text ends with".

I was not able to find a Greek font, so I used English style letters and did my best to match. Don't laugh 😀.

Here is a screenshot of some of the Conditional Highlighting rules.

Aim 2: sort tables according to their cell colour fill.

Not possible to sort on a cell format. However, we can add another column that contains values (text) which will sort the table.

enter these formulas in C2 to G2 and fill down to the end of the columns.

C2 =LEN(A2)

D2 =FIND(",",A2)

E2 = D2−2

F2 ="-"&MID(A2,D2−2,C2−D2+3)

G2 =VLOOKUP(F2,Lookup::A:B,2,0)

Now you can sort the table by Column G to bring all the colours (endings) together

Regards,

Ian.

Hi curlydog,

Catching up on the conversation:

I have attached the rules for the 8 categories of adjective endings.

When sorting to group the same colours together, you are actually grouping by category. If you have numbered these eight categories in a 'standard' order, it may make more sense to sort the entries by Type than by colour. That way the colour groups will be listed in the same order as on the table on which you listed the types and their assigned colours.

[I]n my spreadsheet Sheet 1 (has) a table of Gk to Eng; Sheet 2's table is Eng to Gk; Sheet 3's is A to Ω, Sheet 4's is A to Z.

Taking the table on Sheet 1 as your Main table, the only change needed in the table on Sheet 2 is to exchange the positions of the columns of Greek and English words. As no mention is made of changing the order of these two tables, I would assume a 'logical' order would be by type, which would also group them by colour.

Transferring the content of the table can be done with a simple cell reference to the same cell in the source table (eg. in A1 of the Eng-Grk table, press = to open the formula editor, then type A1

Clck the green checkmark to confirm the formula and close the editor.

Fill the formula right to the last column (where you should, when you release the mouse button, see the content of the last cell in row 1 of the Main table.

Go back to the column containing the English words. Double click the cell to open the formula Editor, Take note of the Column letter in the cell reference, then replace that reference with the column letter of the column of the Main table containing the Greek words.

Make the opposite change in the first cell in the column containing the Greek words, changing the column reference to the column of the main table containing the English words.

Select all of row 1, then fill the formula down to the last row of the table. Add rows if needed to get to the content of the last row of the Main table.

But I haven't yet found a way to automate the alphabetising to be honest. That's my next challenge.

As three of the tables are filled by copying rows of data from the table on Sheet 1, sorting the final two could prove an interesting exercise. Tables filled from other tables by formulas are inherently resistant to post transfer sorting; if the tables are sorted on values that is dependent on the position of that value on the source table, sorting the receiving table will immediately trigger a recalculation, which will return the original values in the original position in the 'resorted' table.

For this stage, my simplest suggestion is to:

• Select all cells of the Greek to English table on Sheet 1. Copy,
• Go to Sheet 3, and select (one click) cell A1 of the Table that i to hold the Α-Ω sort.
• Go to the Edit menu and choose Paste Formula results
• Sort the table on the Greek column.

Repeat with the English to Greek table on Sheet 2

• Select all cells on the source table. Copy.
• Select cell A1 of the table on Sheet 4
• Edit > Paste Formula Results
• Sort the table on the English column.

ALL content editing will be done on the Main table, on Sheet 1.

You will need to add rows to the table on Sheet 2 to match the number of rows in the Main table.

The tables on Sheets 3 and 4 will automatically expand to include as many rows of data (and headings) as are present in the table that the data has been pasted from,

Finally I want to use the colours to pull the words into their correct groups, in another table in another Sheet.

Since each colour matches a specific Type number, grouping by colour is equivalent to grouping by Type number. do this grouping by sorting on type number.

Regards,

Barry

Hi curly dog,

curlydog wrote:

a cell fill colour code to help identify certain word ending types.

Perhaps Conditional Highlighting will do the job:

Please call back with questions and examples of your data.

Regards,

Ian.

Hi curlydog,

Yes I see what you mean. I am still leaning towards Conditional Highlighting as a possible solution. How do you decide what cell colour fill to apply? If you can give us the rules that you use, perhaps we can turn them into rules that Numbers will follow.

I know nothing about Greek, but I think I see a pattern in your screen shot. Does everything after the first comma determine the cell colour fill???

Regards,

Ian.

Hi curlydog,

Columns C, D and E are a long-winded way to extract the endings (in columnF). You could sort the table by column F, but I thought a Lookup table for column G would make it easier for humans to understand (sorting by colours).

You can hide these intermediate columns:

The Lookup table can be anywhere in the document (perhaps on its own sheet).

Regards,

Ian.

curlydog wrote:

I want the formula to also take the cell background format too

Not possible via formula. Are you having trouble rearranging columns via the Numbers interface? It's really easy. Just select the column and with the column selected move the cursor slightly so the column "lifts" and then drag it into the position you want.

BTW, when you say "sheet" I think you really mean table, right? Tables have cells but sheets don't. Sheets have a blank canvas on which you can place tables and other objects like text boxes and shapes.

SG

curlydog wrote:

I see that what Excel calls Sheets, Numbers calls Tables. Not sure how logical that is but hey.... confusing sometimes going between the different programmes. I use both, and Google Sheets too - just to confuse matters even more.

Not quite. A sheet in Excel is similar to a sheet in Numbers. The main difference is that a Numbers sheet contains a blank canvas on which you place tables and other objects. An Excel sheet, instead of a blank canvas, has a big grid of cells within which you can place tables of various kinds (Excel Tables, Pivot Tables, etc.) Tables came late in Excel's development, whereas the Numbers design revolves around tables. It's easy to have many of them on one sheet because you aren't constrained by a big grid of cells. Check out the templates at File > New in your menu for examples.

I'm not sure I entirely understand what you are doing with the vocabulary tables but if you are just flipping the order of the columns then after updating a table I would just duplicate it (click the concentric circles "bulls-eye" upper left, command-c to copy, click on the canvas, and command-v to paste) and then drag the columns into position as described in my post above. This only takes a second or two, keeps the cell formatting, and thus is probably more efficient that trying to automate via formula.

SG

curlydog wrote:

Hi SGIII,

Ok to back track then - I've just gone back to making a fresh spreadsheet and Numbers does call them sheets too. So no confusion then, I did mean Sheets and not Tables.

Are you sure you're not still confused? Sheets in Numbers do not have cells. You're entering your data in tables, not sheets.

Anyway, I'm still confused trying to figure out exactly what you are trying to do. Switching the order of columns is so easy (much easier in Numbers than in Excel) that I'm not sure what new feature is needed! Hopefully you have given Apple a clearer idea.

SG