Change cell background based on cell content in Data Merge from Excel using Affinity Publisher

[DiZastor]

I am new to Affinity Publisher and have created a calendar layout master which I use a data merge from Excel to produce a photo calendar blank where I can choose year and language. I would like to be able to add background colours to the cells for Saturdays and Sundays. Can this be done directly in Affinity Publisher or can it be passed to Affinity Publisher from the Excel data merge file?   Each month is a vertical list of days starting at 1st of the month so Saturdays and Sundays are different for each month and year.

[Old Bruce]

1 minute ago, DiZastor said:

Can this be done directly in Affinity Publisher or can it be passed to Affinity Publisher from the Excel data merge file?

We can do it in directly in Publisher. I doubt we can import from a .CSV or .XLSX file.

[DiZastor]

1 minute ago, Old Bruce said:

We can do it in directly in Publisher. I doubt we can import from a .CSV or .XLSX file.

Thank you for your reply. Can you show me how to do it please? I've looked in table and format table but cannot see any conditional formatting options.

[Old Bruce]

i would suggest you watch this video first.

https://affinity.serif.com/en-gb/tutorials/publisher/desktop/video/494072789/

[DiZastor]

Thank you for your reply. Can you show me how to do it please? I've looked in table and format table but cannot see any conditional formatting options.

 

 

10 minutes ago, Old Bruce said:

i would suggest you watch this video first.

https://affinity.serif.com/en-gb/tutorials/publisher/desktop/video/494072789/

Thank you, I'd already watched that video. I've successfully made my data merge and I've tested it with different languages and years. The only feature I would like is the ability to add background colours to cells based on content or an expression such as if contains "Sat" or "Sun" then cell colour is 10% grey or else clear)

 

[DiZastor]

Thank you, I'd already watched that video. I've successfully made my data merge and I've tested it with different languages and years. The only feature I would like is the ability to add background colours to cells based on content or an expression such as: if cell = "Sat" or "Sun" then cell colour is 10% grey or else clear)

[Old Bruce]

Just now, DiZastor said:

the ability to add background colours to cells based on content or an expression such as if contains "Sat" or "Sun" then cell colour is 10% grey or else clear

This sort of conditional formatting is impossible in Publisher. Regardless of whether or not it is a Data Merge. There is some stuff that can be done with Master Pages being applied to selected Actual Pages (every 7th or 14th page perhaps(?)).

You could try using Paragraph or Character Styles but the success of that actually depends on the design of the spreadsheet.

[DiZastor]

Thank you. I guess the only option is to create a second table behind the first one and manually select and fill the relevant backgrounds to suit. 

[Old Bruce]

Having had some more caffeine I think you could set up a Paragraph Style for the Weekend and set nothing except the Decorations. Choose the proper colour and size location offsets. Now you can apply it using Find and Replace with the Weekend for the Paragraph Style replacement and Days for the Paragraph Style find.

[DiZastor]

Thank you, I'll give it a go.

[DiZastor]

I produced some strange results with gradient fills. After playing around, I've decided the best option is to highlight the weekend cells on each page (month) and do a table fill in grey with about 50% opacity. It's not automatic, but is very quick and I only have to do it once a year for each calendar language. 🙂 Thank you for your ideas and help. Cheers. DZ  

[lacerto]

Color fonts that can have different color depending on the glyph would allow conditional color formatting in the data source, and could be useable at least if you create your tables using the Data Merge Layout control::

The color palette would typically be rather limited unless you'd have some kind of special purpose palette font available, but it could be modified by using e.g. opacity (for tints) and blend modes (like here the Multiply and Linear Light blend modes with a gray rectangle for shading).

For more information, see:

 

[lacerto]

Here is an example of using this technique directly with calendar data. Cell and font color is determined in Excel sheet where it could be applied mostly by calculation. 

In lack of gray rectangle, I used for font coloring an OK emoji with gray background, which works fine when scaled large enough [in situations there is no need to alternate gray with another color, any black shape could be used just by applying opacity; or if rasterizing does not matter, any color emoji with an even shade could be used along with Black and White, HSL or other adjustment or blend mode that discards the color]. Red Large Square is used for holidays and Black Large Square for regular days.

Here is the data merge setup. The month name (which shows "December" because the first records are from the previous year) is pulled with a separate Data Merge Layout control offseting it by 15 records, then backing up when starting to pull day numbers from the same table.

The whole calendar comes finished at data merge:

[DiZastor]

Wow, Thank you for spending the time to show me this. I'll start playing with it right now. My calendar is a slim wall calendar which has each month listed vertically. Each day row is made of 5 cells spread over 3 columns. Day name comes from the excel date number for that day expressed in "ddd" format. All dates are calculated using the excel date numbers. Other languages are calculated using the country language code in the excel cell format eg [$-0809] for English and [$-0816] for Portuguese using a vlookup(). The excel merge sheet has 13 rows, rows 2 - 13 corresponding to a month each. See attached pdfs for sample publisher exports from same publisher merge master.

I'll let you know how I get on. Cheers DZ 

Slim Photo Calendar English sept.pdf Slim Photo Calendar Portuguese setembro.pdf

[DiZastor]

44 minutes ago, DiZastor said:

Wow, Thank you for spending the time to show me this. I'll start playing with it right now. My calendar is a slim wall calendar which has each month listed vertically. Each day row is made of 5 cells spread over 3 columns. Day name comes from the excel date number for that day expressed in "ddd" format. All dates are calculated using the excel date numbers. Other languages are calculated using the country language code in the excel cell format eg [$-0809] for English and [$-0816] for Portuguese using a vlookup(). The excel merge sheet has 13 rows, rows 2 - 13 corresponding to a month each. See attached pdfs for sample publisher exports from same publisher merge master.

I'll let you know how I get on. Cheers DZ 

Slim Photo Calendar English sept.pdf 47.08 kB · 2 downloads Slim Photo Calendar Portuguese setembro.pdf 47.36 kB · 2 downloads

It must seem silly, but I'm struggling to insert an emoji into a cell.

 

5 hours ago, lacerto said:

Here is an example of using this technique directly with calendar data. Cell and font color is determined in Excel sheet where it could be applied mostly by calculation. 

In lack of gray rectangle, I used for font coloring an OK emoji with gray background, which works fine when scaled large enough.Red Large Square is used for holidays and Black Large Square for regular days.

Here is the data merge setup. The month name (which shows "December" because the first records are from the previous year) is pulled with a separate Data Merge Layout control offseting it by 15 records, then backing up when starting to pull day numbers from the same table.

The whole calendar comes finished at data merge:

 

[DiZastor]

I keep returning to my manual method for each post merge calendar, select all cells on each page that need the grey fill and use: Table > Stroke and Fill > Greys > 25%. I'll keep on working on making it interactive from Excel and maybe have it cracked for my 2024 calendars. I still have lots to learn in both MS Excel and Affinity Publisher. Cheers DZ

slim calendar shaded weekends.pdf

[lacerto]

5 hours ago, DiZastor said:

It must seem silly, but I'm struggling to insert an emoji into a cell.

Not at all -- it is a bit tricky especially if you want to enter them by referring the unicode decimal.

You can enter emojis (and other symbols) also by using Apple Character Viewer > Emoji, but using an Excel formula to fetch a desired emoji just by e.g. typing "Orange" can be handy.

You can use Affinity Publisher Glyph panel to show in a pop-up message the Unicode hex value of any glyph. Then use the displayed hex value to convert it to decimal value in Excel using the internal HEX2DEC function, and finally display the glyph itself by using UNICHAR function, as below:

Both methods will show in Affinity Publisher when you import an Excel sheet for data merge. They are previewed already in the Fields panel and on the canvas if you choose "Preview with record" in the Document > Data Merge Manager.

 

 

[lacerto]

Here is an Excel sheet and Publisher Data Merge document that shows how to create a calendar that gets all its data (excluding the image path and caption, which could naturally be included in the same table) from a single Excel sheet, including color formatting. Just to demonstrate the capabilities of this method (no doubt quite stretched), I added font color to make holidays red, and separate line color along with a rectangle that serves as a separator line (using a decoration would have required removal of extra lines for months with less than 31 days):

Here are the required documents and the merged example with the year 2022 data:

calendarsimple.xlsx

calendar_simple.afpub

calendar_simple_merged.afpub

NOTE: On Windows the same files work when using Segoe UI Emoji as the color font (Windows version of Excel cannot display color versions of the glyphs but when they are fetched in Affinity Publisher, the colors show correctly)..

[DiZastor]

Thank you very much... I will look at this later in the day and over the weekend.

In Excel, to end each month on the correct day, I use this test in Cell A31  =IF(A30="","",IF(MONTH(A30+1)=MONTH(A$3),A30+1,"")) where the first of the month is in A3 and 28th of month in A30. A3 contains a formula for first day of selected year =DATE(Cal_Year,1,1). Moon phases come from a NASA table listing moon phases with UTC date and time stamps up to end of 2030. I correct these for country local and DST times where applicable. I use a moon phases font to display the phase symbols.  

Cheers DZ

[lacerto]

On 5/13/2022 at 10:52 AM, DiZastor said:

In Excel, to end each month on the correct day, I use this test in Cell A31  =IF(A30="","",IF(MONTH(A30+1)=MONTH(A$3),A30+1,"")) where the first of the month is in A3 and 28th of month in A30. A3 contains a formula for first day of selected year =DATE(Cal_Year,1,1). Moon phases come from a NASA table listing moon phases with UTC date and time stamps up to end of 2030. I correct these for country local and DST times where applicable. I use a moon phases font to display the phase symbols.

Oh yes, much better if they are based on calculation so there is no need for the kinds of convoluted tricks like my cell separator [EDIT: ...I just could not find a way to avoid empty lines when laying out the table with the Data Merge Layout tool and paragraph decorators)]. I also forgot the moon phases and that is certainly a good source for having them.

[DiZastor]

I've been looking at your calendarsimple.xlsx and have changed a few of your field formulas to show you how I make the year and bank holidays interactive.

If you just change the Cal_Year value to a different year the calendar will update. It even works for the extra Platinum Jubilee bank holiday in 2022.

I'll now explore the cell and character colour merges

 

Cheers DZ

 

calendarsimple DZ Ver1.xlsx

[DiZastor]

I forgot to add that I use Custom formats for DayNumber, DayAbbr, MonthName, and Year; "dd", "ddd", "mmmm", and "yyyy" respectively. Also I've modified the last row, My If() statement was wrong and now is correct.

calendarsimple DZ Ver1.2.xlsx

[DiZastor]

In my Excel file I created my complete calendar to get a visual layout for each month as a 12 page calendar. I then created a sheet which looked up the records for each month with field names as columns and rows as months and the merge creates a page for each month from each row. This means that all my blank fields are passed to Affinity Publisher as blank. I can see a way to put moon phases in by adding them after DayAbbr. I guess if want to put them above DayAbbr I would have to copy the  Calendar Data frame and position the Moon phase field where I want it it this new layer. I will have a play. Are the days in individual Text Frames, not a 31 row table?

I still haven't got my head around the grey fill using the button colour, but I think I've got enough to be going on with. 

Thank you again

Cheers DZ

[lacerto]

13 hours ago, DiZastor said:

I guess if want to put them above DayAbbr I would have to copy the  Calendar Data frame and position the Moon phase field where I want it it this new layer.

I placed it like this:

I used Astrology Pi from Linotronic so change that to whatever font you are using.

13 hours ago, DiZastor said:

Are the days in individual Text Frames, not a 31 row table?

I do not use tables in the merge set up, at all, so it is the Data Merge Layout control that determines the number of records that will be pulled on each page. It has 31 rows and 1 column defined, so it will fetch 31 Excel rows per page. That is why it is necessary to have blank rows in the Excel sheet, except for one column that needs to have data, otherwise Affinity Publisher will skip that record, which is not desirable in this case.

Note that I changed month name formatting back to one that is based on TEXT formula and "mmmm" formatting. For some reason Publisher does not recognize custom cell format in this field even if it does for the DayNumber field, probably because when Excel is used with Finnish regional settings (even when returning English date strings, like "January"), regional formatting syntax must be used, so for me full text custom formatting for month would need to be specified as "kkkk" (for "kuukausi", literally "moonperiod"), while with your regional settings the correct syntax is "mmmm".

13 hours ago, DiZastor said:

I still haven't got my head around the grey fill using the button colour, but I think I've got enough to be going on with.

When gray is used as cell background, and the alternating state of the layer is "no background", the easiest method to have gray fill (and with exact shade) is to simply just use "Black" and then specify opacity value for the layer (as the opacity would only be used with one color value). When gray needs to alternate with black or some other color, like in one of my examples above where "overflowing" day numbers were printed in gray and regular ones in black, this cannot be done so for these purposes there needs to be directly a glyph that has gray color in it (which I picked from the "OK" button in that case, and scaled up and positioned it so that the gray area is shown in the graphic element where color needs to be applied).

13 hours ago, DiZastor said:

I then created a sheet which looked up the records for each month with field names as columns and rows as months and the merge creates a page for each month from each row.

I am not sure if I understood, but I suppose there are multiple ways calendar data can be fetched and arranged. One possibility would also be using an Excel sheet to generate the data but then copy paste it in Word and tag elements with paragraph and character styles, and finally convert the table to tab separated text so that when placed in Affinity Publisher, the paragraph styles would have bottom decoration that creates the separating line, and character styles would define the font color and character decoration simulate cell background color. The first day of the month would start on a new page by virtue of using a flow break defined in a paragraph style. That would allow flowing fully formatted table-alike data in one go and avoid the problem caused by missing support for multi-page tables in Affinity Publisher. But when there is more complex formatting involved, data merge based composition is most versatile as the fields can be placed in separate layers and positioned accurately at desired places. 

calendarsimple_moonadded.xlsx

 

[DiZastor]

I'll see if I can get my head around Data Merge Layout (DML). It seems to be much faster and simpler than the table method I am currently using. So in simple terms, everything you put in the first row of the DML will be filled in the rest until end of page and a new page started automatically. My current Excel Slim calendar layout has a block of columns for each month. I guess that I should change it to 12 x 31 rows for the days of each month plus another 12 x 1 for the month header row "January and Year". I change languages in Excel using a similar vlookup() to the one for the year where one of the columns is a number starting at 1 increasing by 1 for each each language, a column for country which internally deals with day and month names directly from the excel date number.  I set out bank holidays for each chosen country on a grid based on 5 columns then use offset() to calculate which 5 column set to use for that language. This picks up the correct bank holidays for that particular country and puts them in the correct position in the calendar. I guess you are Finnish, so I will create one in Finnish and show you what I mean using my original slim calendar layout (because I know that works haha).

Cheers DZ