Copy cell format in a formula, Numbers

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.

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

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.

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

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,

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.