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

  • Thread starter Thread starter duzhidian
  • Start date Start date
D

duzhidian

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
 
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
 
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.
 
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
 
Glad to be of help.

Pete

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.





- Show quoted text -
Click to expand...
 
Back
Top