Trip Calendar Auto-formatting

Hello bees_knees. Welcome to the forum.

It is almost always helpful and expedient to upload a sample workbook directly to this forum - this should be a cut-down version of your real data, but with the same layout and containing enough data to demonstrate the difficulties that you are facing.

If you are not familiar with how to do this ...
To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

click “Go Advanced” (next to Post Quick Reply – bottom right),
scroll down until you see “Manage Attachments”, click that,
click “Browse”.
select your file(s)
click “Upload”
click “Close window”
click “Submit Reply”

The file name will appear at the bottom of your reply.

(Of note: The 'paperclip' icon has not worked for some time. So please save yourself some grief.)

I believe that the following formula will do what you want:

Formula:

Please Login or Register  to view this content.

Note that the formula is only applied through row 20.
When you get ready to apply to row 26 > select cell B20 > press Ctrl + c > select cell B26 > press Ctrl + v > drag the fill handle over to cell AF26
Note that you'll need to modify the formula to include one more column than the columns containing countries in row 1 and you'll need to put a value in row 3 of the last column.
Note that you can hide the zeros (see H20:AF20) using conditional formatting.
Let us know if you have any questions.

Another way.

In the attached there is a copy of sheet 'Calendar' ... 'Calendar (2)'.

Row 1 and 2 includes added countries and Days in location. Rows 2 and 3 existing formula are copied across.

In B8 this formula filled across column AF returns countries as days in location indicate

Formula:

Please Login or Register  to view this content.

Then B8:AF8 copied and pasted into cells B14 and B20.

(Note the merged cells have been removed in row 20. Merged cell should be avoided like the plague. They create havoc in formulas.)

Now as countries are added change the range references in the formula for the countries ... or
create some dynamic named ranges in Name Manager. These will size themselves according to amount of data and save having to edit the formula above.

Please let me know if you are interested in that.