Trip Calendar Auto-formatting

[bees_knees]

Hey y'all,

I am trying to create a trip calendar to help organize a big multi-country excursion next year. Basically I would like my trip calendar to have the 'Country' row updated as I change the 'Days in Location' row so that I don't need to go in and manually change the country row for all countries that are chronologically behind my current one as I am moving things around.

I have something of a master plan section setup above a calendar section and would like the information in the calendar (specifically the 'country' row) to be updated as I change the master plan section.

How I have thought about doing this is to have my 'Country' row determine if the value above it in the 'Trip Day' is between the 'Start Day' and 'End Day' values listed in the master plan section. At this point I know I could write a super complicated IF function to get it done with and statements listed for all of the countries in my trip, but I feel like there must be an easier way to do it, maybe with a CHOOSE function?

Once this is done I would like the all of the cells in the 'Country' row to me merged and centered into one, but I figure that could be done pretty easily with a macro.

Calendar_snip.JPG

Any help would be appreciated as I am a true newbie to the forum!

[FlameRetired]

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.)

[bees_knees]

Hey FlameRetired,

Thanks for the advice. I've worked a little bit more on the problem and got the start to a formula that works but it's hairy and complicated so I don't know if there is a better way.

Rows 8 and 14 have my formula in there and you should be able to see that it does return the information I want, but if I were to add more countries to the selection for the formula it would be getting really long really quick! Is there any better way to do what I am currently doing with that formula?

[JeteMc]

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

=INDEX($B$1:$I$1,AGGREGATE(14,6,(COLUMN($B$1:$I$1)-COLUMN($A$1))/($B$3:$I$3<=O7),1))

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.

[FlameRetired]

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

=IF(B7="","",INDEX($B$1:$L$1,MATCH(0,INDEX(--(B7>$B$4:$L$4),0),0)))

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.