Copying

[neilangelo]

I have a situation where I have a list of items listed vetically a
follows in excel eg

A B C

1 Month Principal Interest
2
3 April 300 50
4 May 400 40
5 June 500 30
6 July 600 20
7 August 700 10

However I have a table that is created as follows

A B C
D
9
10 April Principal May Principal June Principal
July Principal
11
12 =B3

I want to copy the formula in Cell A12 to B12, C12 and D12 such that i
will automatically recognise cell B4 for May principal and cell B5 fo
June Principal etc. My spreadsheet in reality is very large s
achieving this will cut down the amount of work I have to do.

Thank

 

[bob777]

Have you thought of creating your second table using the sumproduc
worksheet function

 

[neilangelo]

How would that sum product worksheet function work

 

[vezerid]

neilangelo,
Ignoring for the moment your comment that the spreadsheet is very large
(it naturally points to other directions, e.g. subtotals etc). What you
seem to want is a transposition problem. You have 12 values (no, less,
you start with April) in a column and you want them transposed
horizontally.

In A12, in place of your current =B3 formula, use:

=OFFSET($B$2, COLUMN(), 0)

Copy accross.

Does this help?
Kostis Vezerides

 

[neilangelo]

Could you give me a working example as to how this Offset function work
so that I can better able apply it to my workshee

 

[vezerid]

neilangelo,

Actually I did give you a working example. Right where you are
attempting to enter =B3, you should enter the suggested formula.

ABout OFFSET(): it is used when we want to refer to cells by position
relative to another cell. Say you want to generate the numbers 1, 2, 3
etc in a column. You could start with the number 1 in A1 and then,
below, in A2, you would enter

=A1+1

This formula can be copied down so that it will become =A2+1, =A3+1
etc, for each cell down A:A. An alternative method for the formula in
A2 would be:

=OFFSET(A2, -1, 0) +1

I.e. I want to add 1 to the value of the cell which is located -1 rows
(i.e. above) A2 (the current cell) and 0 columns to the right of A2.

What is the difference? Most times you would use the simpler formula.
However, if you delete, say A3, then all the cells below will produce
#REF!. This is because there still exists a cell called A3 but it is
not the one originally, physically, referred to by A4 (which has now
moved to A3). However, with the OFFSET() option you are not referring
to another cell but to yourself, and calculate a relative position.
Thus, with this technique, you can freely delete cells.

In your case, the problem is that you want to transfer rows to columns.
So this formula is using the column number of the destination, to tell
Excel how many rows below the original cell you want to look for in
order to get the desired number.

HTH
Kostis Vezerides