Multiplication grid?

[BuffaloGuy]

I don’t know how to phrase this question, but what is a fast way to keep a constant at the top of a table, multiply down the table, then slide the column to the right, only changing the constant at the top? It is made more complicated with thousands of rows and dozens of columns where the $ is more difficult to use.
My equation is complex than below, but that is the gist.

	A	B	C	Z
1	A1	B1	C1	Z1
2	A2	B2	C2	Z2
3	A3	B3	C3	Z3
400	A400	B400	C400	Z400

 

[Cubist]

Which version of Excel are you using?
Show the current formula being used.

 

[Logit]

Here is a VBA macro approach :

Option Explicit

Sub CreateLetterNumberMatrix()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim i As Long, j As Long

    ' Set the worksheet to work on
    Set ws = ThisWorkbook.Sheets(1) ' Adjust the sheet index or name as needed

    ' Find the last row in column A and last column in row 1
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    ' Loop through the numbers in column A
    For i = 2 To lastRow
        ' Loop through the letters in the first row starting from column B
        For j = 2 To lastCol
            ' Combine the letter in the first row with the number in column A
            ws.Cells(i, j).Value = ws.Cells(1, j).Value & ws.Cells(i, 1).Value
        Next j
    Next i

    MsgBox "Matrix created successfully!"
End Sub

 

[kvsrinivasamurthy]

For 365version, In B2

=LET(a,B1:E1,b,A2:A5,MAKEARRAY(ROWS(b),COLUMNS(a),LAMBDA(ro,cl,INDEX(a,cl)*INDEX(b,ro))))

For other versions, In B2 copied to full range

=$A2*B$1