Excel spread sheet : related to formula copying

manjuvenamma

I have a table of numbers say 12 X 12 i.e A1 cell to L12 cell. All of them have numbers filled in.

I have to create another table of identical size. first row and first column numbers are just duplicated in the new table i.e.A1 to A12 (first column) A1 to L1 (first row) cells are just copied into the new table without any changes.

All other cells of the new table are to be populated as follows:
for example: cell corresponding to b9 is to replaced by the product of three numbers (i) b9,
(ii) a9 (first element of the same row) and b1 (first element of the same column).

Another example: j6 is to replaced by the product of j6, a6 (first element of the same row) and j1 (first element of the same column).

I can do this manually by writing formula in each cell. I want to do automatically by writing a formula in one cell and copying the same in all other cells of interest.

Can this be down and how?

phinds

it can be done very easily w/ VBA but I wouldn't know how to do it with a cell formula although I wouldn't be surprised if it COULD be done w/ a cell formula

EDIT: I've done the VBA --- if you want it, send me an email (NOT a PM)

Andre

There is a simple copy paste for that.

Write the formula in the first cell. Note that there is a little black dot in the lower right corner of the cell where the cursor is. Aim the mouse on that and the pointer changes to a small cross. Now drag the mouse over all the cells where you want the formula copied.

Note that the relative geometry of the copies stay in tact. so if you drag the formula =a6*a7 one to the right and one down, the copy will read =b7*b8 etc

If you want absolute geometry, you put a $ before the row or column that should remain pointing to the same column ($b6) or row (b$6) or both ($b$6) if the copy needs to look at the single cell only.

manjuvenamma

Andre, thanks for the reply. Here neither I need neither the absolute addressing nor the relative addressing. Basically in the new table (except the first row and the first column) every other cell is to be replaced by the product of corresponding cell in the original table,
the first cell of the corresponding row and the first cell of the corresponding column in which this cell sits. So it changes with each cell but NOT in the relative fashion.

manjuvenamma

Thank you, very much, Andre. I got it now and it is working. I was thinking that we can use $ in singly like $A37. Now I realised we could used like A$37 too.