I am trying to build a heat map of a system I am working on. Here is a sample file with the bare bones:
File Hosting - Free File Upload. Filehostfree
On the model tab I have the layout I want to use. This is all hard coded and referred to throughout the document to make changes a one-place only thing.
On "test data" I have data that comes from our testing team. The data refers to processes and use cases. A use case may occur in one or more process flows but a failure in that test case will be specific to the process / use case intersect. In this example Process flow 2, use case 2 has failed.
I therefore want to colour all cells on the heat map green, except C2 which should be red.
In attempting this I have used match and indirect to look up the address of the cell I want to colour in "test data" column E using the formulas in C and D which look up the row with a simple match statement, and then the column within that row using an INDIRECT to build the ADDRESS in Column E.
Now on the sheet HMOC ( heat map overlay calculator was ... clumsy! ) I have a statement that says:
=VLOOKUP(TEXT(CELL("ADDRESS"),""),'Test Data'!$E$2:$F$10,2,FALSE)
Get my address as text, go and look up that text in the array E2:F10 on the test data sheet and get the value in the second column.
This is where things go a bit crazy.
Firstly the HMOC sheet seems to calculate however ALL cells are marked as "pass" which should not be the case, Cell C2 should look up $C$2 in the test data sheet and get the value "Failed" from the second column.
Next, when the HMOC sheet says "pass" head over to the heat map sheet and resize the columns. Everything flips to "#N/A"
This bit I really don't get. I know there is a recalculation going on but nothing has changed, at all!
There may be a much easier way to accomplish what I am trying to do here and I would be very grateful for any input on how, and if this way is workable, what is wrong with my implementation?
Thanks.
Ed Moore.
File Hosting - Free File Upload. Filehostfree
On the model tab I have the layout I want to use. This is all hard coded and referred to throughout the document to make changes a one-place only thing.
On "test data" I have data that comes from our testing team. The data refers to processes and use cases. A use case may occur in one or more process flows but a failure in that test case will be specific to the process / use case intersect. In this example Process flow 2, use case 2 has failed.
I therefore want to colour all cells on the heat map green, except C2 which should be red.
In attempting this I have used match and indirect to look up the address of the cell I want to colour in "test data" column E using the formulas in C and D which look up the row with a simple match statement, and then the column within that row using an INDIRECT to build the ADDRESS in Column E.
Now on the sheet HMOC ( heat map overlay calculator was ... clumsy! ) I have a statement that says:
=VLOOKUP(TEXT(CELL("ADDRESS"),""),'Test Data'!$E$2:$F$10,2,FALSE)
Get my address as text, go and look up that text in the array E2:F10 on the test data sheet and get the value in the second column.
This is where things go a bit crazy.
Firstly the HMOC sheet seems to calculate however ALL cells are marked as "pass" which should not be the case, Cell C2 should look up $C$2 in the test data sheet and get the value "Failed" from the second column.
Next, when the HMOC sheet says "pass" head over to the heat map sheet and resize the columns. Everything flips to "#N/A"
This bit I really don't get. I know there is a recalculation going on but nothing has changed, at all!
There may be a much easier way to accomplish what I am trying to do here and I would be very grateful for any input on how, and if this way is workable, what is wrong with my implementation?
Thanks.
Ed Moore.
