Trying to Create a Matrix

[Fear Naught]

Hi Everyone,

I'm trying to create a Cross Tab type of Matrix in Excel. To put this in to context I have a list of license prices but want a matrix (similar to to distance chart that you get in road atlases) with license quantity and price across the horizontal access and and also down the verticle access with a calculation of the price indifferece between each level of license in the matix grid.

Whilst it is possible to do this manually I'd like to be able to make it easy but of course when copying cells the row and column references wont change in the correct way. I have all single prices in the verticle column but I can't get them to transpose easily in the horizontal column.

I have never used the R1C1 method and wondered if perhaps I need to for ths project.

Any help would be most appreciated.

 

[Brianwarnock]

If I understand correctly your problem is in trying to copy formula over an area.

I would use a simple macro to chieve what you want, highlight the area and run the following which assumes column A and row 1

Sub blah()
For Each c In ActiveWindow.RangeSelection
c.Value = Cells(c.Row, 1) * Cells(1, c.Column)
Next c
End Sub

Brian