Creating a huge multiple table diagonally, not row by row or column by column?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

duzhidian@gmail.com - 27 Jan 2008 20:18 GMT

Dear All:

I want to creating a multiple table using Excel 2007, as follows:

1 2 3 ...
1 (1) (2) (3) ...
2 (2) (4) (6) ...
3 (3) (6) (9)...
.
.
.

Every position is the product of leftmost multiply top most value. I
can use formula to do it row by row or column by column. But I
cannot do it diagonally. The reason I want to finish this table one
step is there are too many rows and columns to do them by row or by
column only.

Thanks.

Du

Reply to this Message

Pete_UK - 27 Jan 2008 20:29 GMT

In B2 enter this formula:

=$A2*B$1

Copy this across the row into C2 to however many columns you have.
Then highlight B2 to your last cell and copy/paste this range into B3
downwards, however many rows you have.

Don't understand what you mean by "...there are too many rows and
columns to do them by row or by column only..."

Hope this helps.

Pete

On Jan 27, 8:18 pm, "duzhid...@gmail.com" <duzhid...@gmail.com> wrote:

> Dear All:
>
[quoted text clipped - 17 lines]
>
> Du

Reply to this Message

duzhidian@gmail.com - 27 Jan 2008 20:37 GMT

Thanks. I got it. I thought I can only copy one row or column at a
time. I did not realize I can copy the whole row downwards.

> In B2 enter this formula:
>
[quoted text clipped - 34 lines]
>
> > Du

Reply to this Message

Gord Dibben - 27 Jan 2008 20:57 GMT

If you are interested, here is a macro that will do the same thing without
formulas or the numbers in Row 1 or Column A

Sub FillNums()
'to fill rows and columns with numbers from 1 to whatever
'across or down
Dim nrows As Integer
Dim ncols As Integer
On Error GoTo quitnow
RowsorCols = InputBox("Fill Across = 1" & Chr(13) _
& "Fill Down = 2")
Num = 1
nrows = InputBox("Enter Number of Rows")
ncols = InputBox("Enter Number of Columns")
If RowsorCols = 1 Then
For across = 1 To nrows
For down = 1 To ncols
ActiveSheet.Cells(across, down).Value = Num
Num = Num + 1
Next down
Next across
Else
For across = 1 To ncols
For down = 1 To nrows
ActiveSheet.Cells(down, across).Value = Num
Num = Num + 1
Next down
Next across
End If
quitnow:
End Sub

Gord Dibben MS Excel MVP

>Thanks. I got it. I thought I can only copy one row or column at a
>time. I did not realize I can copy the whole row downwards.
[quoted text clipped - 37 lines]
>>
>> > Du

Reply to this Message

Pete_UK - 28 Jan 2008 09:04 GMT

Glad to be of help.

Pete

On Jan 27, 8:37 pm, "duzhid...@gmail.com" <duzhid...@gmail.com> wrote:

> Thanks. I got it. I thought I can only copy one row or column at a
> time. I did not realize I can copy the whole row downwards.
[quoted text clipped - 39 lines]
>
> - Show quoted text -

Reply to this Message