I am an IT project Manager actively involved in designing excel run sheets for executing server migrations. I have a standard excel run book which I usually use to execute migration events and wanted to automate it so that it does not take me a lot of time to manually change the details. Can someone help me with the following :
Excel Sheet Setup : Following are the key tabs in the excel sheet I have
Sheet 1 : Master Server List which has the following columns
- Server Name
- Server IP
- Group ID
Sheet 2 : Migration Methodology consists of the following drop down list
- Storage vMotion
- Live vMotion
Sheet 3 : two columns Column 1 - Migration steps for each type of methodology Column 2 : duration of each step in hh:mm:ss format. Note that the steps are common across each method which are repeatable for each server in the group ID mentioned in the server list sheet
Storage vMotion Steps
- Step 1 duration
- Step 2 duration
- Step 3 duration
- Step 4 duration
Live vMotion Steps
- Step A duration
- Step B duration
- Step C duration
- Step D duration
Requirement:
===============
1. User should fill up the Server List Tab with all columns with the IP Address column being optional
2. User will then select the Migration Methodology
3. There will be a button "Generate Detail Run Sheet"
Based on the selection the user makes multiple sheets should be created populating the predefined steps for each group in the master server tab. E.g. If the server list tab has 3 groups and selects Storage vMotion as the method and clicks "Generate Detail run Sheet" button 3 separate excel tabs (one for each group) should be created populated with the Steps 1, 2, 3 & 4
Also if there is a new method to be added the user should be able to append steps and duration in sheet 3 and click add method. This should populate the drop down and should be usable
The generated sheet will have the following columns
Column 1 - Prepopulated Steps from Sheet 3 ( based on methodology )
Column 2 - Start Date and Time ( Empty to be populated manually )
Column 3 - Duration ( Populated from sheet 3 )
Column 4 - End Date and Time ( Calculated by adding Column 2 and 3 )
USE CASE SUMMARY
=========================
In the beginning the sheet will have the following tabs
Index - Manually created
Introduction - manually created
GO/NO GO Checklist - Manually Created
Master Server List - Manually populated ( have marked columns that are mandatory to fill up in Grey. If these columns are not populated then there will be an error in generating run books )
Migration Methodology - Will have the list of all Migration Methods and will also have provision to add a new method
List - Will be a hidden list which has all the Migration methods
Migration Methodology Steps - Will have steps for each migration method
Requirement 1 : generate Run Book
================================
STEP 1 : User fills up mandatory columns in Master Server List
STEP 2 : User selects Migration Methodology from Master Server List
STEP 3: User hits the button generate run book
Expected output : This should generate detailed run books as shown in the sample sheet
Wave#1 Group 0
Wave#1 Group 1
Wave#1 Group 2
Wave#2 Group 1
The Wave number comes from master server list and the Group number also comes from Master Server List
The rows highlighted in Grey in the Detailed runbooks will remain exactly the same and will not change. The steps will be populated for each server in the Group. In the sample sheet there are 2 servers in each group but there may be more servers in the group. Also there may be multiple waves so the tab needs to be named accordingly.
Requirement 2 :
==============
Assume there is a new Migration method in the market and we want to add this in the Excel.
Step 1 : User appends the migration method in Migration Methodology Steps Tab
Step 2 : User goes to the tab Migration Methodology and clicks on the button Add methodology
Expected outcome
The list in the Migration Methodology should be updated to reflect the new method
User should be able to generate run book based on this methodology
Attached is the sample Excel Spreadsheet