Creating schematic... how?!

Hi all. I am trying to create a macro (or alternative) that produces a schematic.

The schematic has a vertical height of 100%, and a width of 100%. The height will have a currency value attached to it, but for now (!) a percentage is fine.

A schematic looks something like this:

schematic.JPG

Each rectangle is called a layer. Each layer is made up of three parts:
The attachment point - the percentage that the bottom of the rectangle hits on the vertical axis
The limit – how far the layer extends up (ie, the percentage of the top of the rectangle minus the attachment point)
The order – how far the rectangle extends horizontally.

So for the layers in the diagram:

Please Login or Register  to view this content.

I would like to create a macro, or otherwise, that creates such a schematic within the Excel workbook (ie, not a new chart, but draws lines/boxes around the cells to represent the schematic – as data will be entered into it). It would take the three data points from a table, with a layer reference (eg a, b, c) if necessary. I would also like the option to NOT put an order in and for excel to divvy up the columns proportionately, but I think that makes it a lot more complicated.

The other potential pitfall is that if a layer were to have a very small order, for eg 1%, the "box" it drew would still need a few rows/columns in it for data to be entered.

That’s the bones of it! Please let me know if this is possible, and if so, how to go about it…

Many thanks in advance
Ed

Hi all - I have made a spreadsheet showing what we are working towards

schematic example.xlsx

Please ask if you're unsure what I'm talking about... thanks in advance