Case Study 249 - Automated Repair for a Winelist
Background
A restaurant company approached us because they needed help with their wine list. The sommelier stored their list in an Excel workbook with multiple tabs. Each tab represented a page of their wine list. The sommelier needed help maintaining the consistency of the formatting on the tabs.
Over time, the sommelier added and removed wines from each page and changed the number of tabs. With each modification, the page formatting often changed inadvertently.
The sommelier wanted a macro that could completely reset all the formatting on all the tabs so that every row that was a heading, for example, looked the same as all the other heading rows. The macro also needed to anticipate adding and removing tabs in the workbook.
Solution
We started by adding a column to each tab, allowing the sommelier to identify the formatting he wanted applied to each row. There were four types of rows: headings, subheadings, notes, and, most importantly, wines.
Then, we added a tab at the beginning of the workbook with a table that allowed the sommelier to assign the row height and font size for each of the four types. See the image below. We also allowed for additional row types if the sommelier needs more in the future.
Finally, we wrote a macro to loop over all the tabs’ rows, automatically setting the row height and font size to match the predetermined sizes from the first tab.
Update
The sommelier loves the macro because it saves him so much time. Recently, the staffer on the project visited the restaurant, and the sommelier greeted them with a huge smile. When asked about the macro, the sommelier remarked, “I couldn’t be happier.”
Let us know if you have a process that could use automation. We will be happy to help.