Excel Christmas Tree 2015

It’s only a week until Christmas Eve, and to help you celebrate the holidays, I have updated my Scroll Bar Christmas tree. No macros are needed, but this version uses customizable icon sets that are available in Excel 2010 and later versions.

This will be my last blog post until the new year, so happy holidays, and remember to cell-ebrate safely!

Excel Christmas Tree – no macros http://blog.contextures.com/

The Excel Christmas Tree

Here is a screen shot of the updated Christmas Tree workbook.

christmas2015tree01

This version uses the conditional formatting icons from the 2009 version, and the gifts and sequential tree trimming from the 2010 version.

Linked Picture

In the 2015 version, there is a picture of Santa that appears at the end of the sequence. John Walkenbach suggested that in 2009, and better late than never!

christmas2015tree04

This is a linked picture, that refers to the SantaPic named range. That range is an empty cell (J1), until the Spin Button reaches a setting of 59. After that, it shows the picture from cells J4:L12.

=IF(Tree!$G$3>=59,Lists!$J$4:$L$12,Lists!$J$1)

christmas2015tree05

Conditional Formatting Icons

On the tree, the lights, tinsel and star at the top all appear based on conditional formatting settings.

In a merged cell at the top, a Star icon appears if that cell’s formula returns a 1. I don’t usually use merged cells, but will make an exception for Christmas.

=IF($G$3>=VLOOKUP(ROW(),RowLU,3,TRUE),1,0)

Then, using customized icon sets, a value of 1 or greater shows a star, and other values show no cell icon.

christmas2015tree03

Lights and Tinsel

The conditional formatting for the tree lights and tinsel strings works the same way. A lookup table sets the values for each row number, with higher numbers appearing later in the sequence.

=IF(VLOOKUP(ROW(),Lists!$F$2:$G$17,2,TRUE)>$G$3,0,(RAND()*100)+1)

christmas2015tree07

There are 3 possible tree ornaments/lights, and the RAND function is used to mix them up. I add 1 to the result, to ensure that the result is never zero.

=IF(VLOOKUP(ROW(),Lists!$F$2:$G$17,2,TRUE)>$G$3,0,(RAND()*100)+1)

christmas2015tree06

The tinsel strings use the Zero Filled Boxes icon.

christmas2015tree10

Start the Tree Trimming

There is a spin button on the worksheet, and you can click that to move up or down in the tree trimming sequence. The previous version had a scroll bar, but that was acting very flakey (getting stuck, disappearing, etc.), so I got rid of it.

The spin button is linked to cell G3, and you can clear that cell if you want to start over. Or, if you’re impatient, type 60 in that cell, to see the completed tree.

christmas2015tree08

Tree Trimming Messages

Just like the 2010 version, this tree has messages that appear, based on the Spin Button value. You can change the messages, or add new ones, in the table in the workbook.

christmas2015tree09

Download the Christmas Tree 2015 Workbook

To see you the Christmas Tree works, you can download the sample file from my Contextures website. On the Excel Scroll Bar Christmas Tree page, go to the Download section, and get the 2015 version of the file. The zipped file is in xlsx format, and does not contain any macros.

Happy Holidays!

_____________________

Excel Christmas Tree – no macros http://blog.contextures.com/

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *