Excel chart like Battleship for space filling demonstration

Question

I tried almost everything that I could remember. I have some data like this in an Excel file:

Item 1  Item 2
A1       B1
A2       a2
A3       a3
B2  

I want to create a chart that updates constantly with spaces from A1 to G7 that will get this kind of result (kind of like in Battleship):

Any idea how to do this?

Answer 1

You could use a conditional formatting rule like:

=SUMPRODUCT(--EXACT($A2&B$1,Lookup!$A$2:$A$10))

with a fill colour of green, and another one

=SUMPRODUCT(--EXACT($A2&B$1,Lookup!$B$2:$B$10))

with a fill colour of orange.