I also used some of the new Illustrations from that menu on the attendee view sheets. ![]() To access them, go to the Insert tab on the Ribbon, click on Icons, then the Cutout People tab. Excel has a large library of cutout figures that you can add to spruce up your reports, forms, or presentations. A Cutout PersonĪs you saw in the Row Configuration Seating Planner, I used a cutout person to hold a sign. As you can see in this rule, the EVEN function rounds up a number to the nearest even number, testing that against the current row number. ![]() Click to enlargeĪbove is an image of the conditional formatting manager. To implement the EVEN function, I used it with conditional formatting to shade alternate rows. For example, if you want to have 6 seats per row instead of 8, the planner instantly rearranges the optimal seating plan in a reformatted spill range. The dynamic formulas allow us to change the properties such as number of seats or the order of placement. These are two very powerful functions to that help to arrange the participants in the two seating formats. I use a lot of dynamic array functions in this model, but particularly SEQUENCE and SORT BY functions. I've used the data in the cards to populate my spill range (see the image of the visual seating chart below). We can extract this data into formulas and we can also use it in spill ranges. You can see the contents of the data in the card view by clicking on the icon in the cell. This is a new feature of Power Query that allows you to store information from multiple columns in a single cell. The first “ingredient” in my hash is custom data types. Let's take a look at how the four Excel Hash ingredients are used in the planner. And you can customize the setup to match your venue by changing the number of seats per table, rows, and/or number of tables. It also keeps everyone in their group sitting together. The planner optimizes seating arrangements, allowing you to use the least number of tables or rows. The planner also has front-end views that attendees can use to find their seating assignment after selecting their name from a drop-down list. There are admin pages that allow the event coordinator to control the layout and sort order. It can also include columns for attributes about each person like attendance, grade point average, performance, age, primary language, group name, etc.ĭynamic Array formulas are then used to create a spill range in either the row or table views. The planner starts with a list of names in an Excel table. This was also challenging, but I was able to use the SORTBY function and Solver Add-in to optimize the sort order. I also wanted a way to have groups or families to sit together while using the least number of rows or tables. I mentioned it to my wife, and she said, “what about weddings?” This became quite a challenge, but the planner evolved to handle round table seating for events and conferences. So I wanted to give others a chance by creating a seating chart planner that easily allows the teacher to sort by OTHER attributes besides last name. Ultimately, I think it's a good thing to sit up front. This also meant going first for presentations and discussions. Since a lot of teachers create seating charts based on alphabetical order by last name, I was pretty much guaranteed a seat up front. With the last name of Acampora, I almost always sat in the front of the classroom. Here are links to Episode 1and Episode 2 as well, which you can also learn a lot from. Here's a playlist on YouTube of everyone's entries. Link to YouTube playlist of everyone's entries The ContestantsĪs in previous years, this friendly competition included some really sharp minds and Excel pros, so I encourage you to like and subscribe to their channels and blogs to further increase your Excel knowledge. I chose to use Dynamic Array Formulas and Power Query Custom Data Types along with the required Cutout People and EVEN function. The rules are to use 4 of the following Excel features to create a solution, with two of those features being required. This is the third time we've done it, and it's a really fun way to learn different uses for features in Excel. It's a competition between my fellow Microsoft MVPs and YouTubers where we're given a list of Excel features (our ingredients, so to speak), and we have to create a solution in Excel using all of those features. This solution is my submission for Episode 3 of Excel Hash. The planner also has admin views to control the seats per row/table, room layout, sort order, and group order/optimization. ![]() ![]() There is also a “Row View” for row/aisle seating layouts. Here is a preview of the “Table View” for the seating chart planner that the attendee can use to find their seat. Seating-Chart-Planner-Excel-Hash-Feb-2021.xlsx Download Seating Chart Planner
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |