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

On Jan 27, 8:18*pm, "duzhid...@gmail.com" <duzhid...@gmail.com> wrote:
> 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

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.


On Jan 27, 3:29 pm, Pete_UK <pashu...@auditel.net> wrote:
> 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:
>
> > 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

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

On Sun, 27 Jan 2008 12:37:12 -0800 (PST), "(E-Mail Removed)"
<(E-Mail Removed)> 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.
>
>
>On Jan 27, 3:29 pm, Pete_UK <pashu...@auditel.net> wrote:
>> 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:
>>
>> > 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

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.
>
> On Jan 27, 3:29 pm, Pete_UK <pashu...@auditel.net> wrote:
>
>
>
> > 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:
>
> > > 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- Hide quoted text -
>
> - Show quoted text -