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]

Obsolete.

[lacerto]

Obsolete.

[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]

Obsolete.

 

[lacerto]

Obsolete.

[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]

Obsolete.

[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]

Obsolete.

 

[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