A guide and Excel sheet about Dynamic Transpose to have an overview to configure and maintain test data, and a technical sheet for IT-systems
What is a transpose
Transpose is a copy & paste function in Excel to switch switch data between columns and rows:
The problem is that it is not dynamic. If you change the data in one cell, then it stays the same in the original cell:
So what I have done is to make a dynamic transpose.
Why do I need a transpose?
Most programs read a row at a time in Excel. Which means test cases have to be structured like this:
Which becomes unreadable with many columns:
But with a dynamic transpose, we can get a good overview, with the same test data:
Which means we can have an overview sheet:
and a technical sheet (that is transposed and always up to date):
And the technical sheet can be read by all programs, while you edit the overview sheet!
How to do it?
It is simply done with the following formular:
=IF(
INDEX(
Overview!$A$1:$GZ$302;
COLUMN();
ROW()
)="";
"";
INDEX(
Overview!$A$1:$GZ$302;
COLUMN();
ROW()
)
)the INDEX is a look up, where we lookup in area Overview!$A$1:$GZ$302; and switch between row = COLUMN() and column = ROW().
The IF is about checking if a field is empty, and if it is, then it should give value “”. If we don’t do so, then all the empty spaces will be set to “0” (the system reading it will also read as 0 instead of an empty cell.