Creating a table of formulas without having to hand-key in everything?

[ShannonO]

Okay, I'm an Excel newbie. I have a matrix of data that I want to use to create another matrix of data. I have the formula worked out how I want it, so that part is fine. WHat I can't figure out how to do is automatically populate the results table, instead of hand-keying each cell with the formula. Here are the corners of the results table I want:


=SUMPRODUCT($B$6:$B$299,B$6:B$299) ..... =SUMPRODUCT($B$6:$B$299,AJ$6:AJ$299)

.
.
.
=SUMPRODUCT($AJ$6:$AJ$299,B$6:B$299) .... =SUMPRODUCT($AJ$6:$AJ$299,AJ$6:AJ$299)

In other words the first row of the results matrix would look like this:

(sumproduct of (first column, first column), (sumproduct of (first column, second column)...sumproduct of (first column, column n-1), sumproduct of (first column, column n)

I have no idea how to make this table without filling the forumal by hand for each cell. Using the drag-copy feature hasn't worked for me because it changes rows. :-/

I'd appreciate any help you can provide. I'm very much a newbie, so I may have trouble parsing responses in Excel speak...

[ShannonO]

I have figured out that I can create the first column by hand, then drag-copy the rest of the columns. But with hundreds of rows, that's still a lot of hand work. :-/

[table83]

Data table is probably the easiest way to do what you are trying to do. They are not hard, just a little tricky to figure out the first time.

The locking in on a row or column also help. Thats what the $ signs are for. If you can properly use them you may be able to fill out a whole row then drag the whole thing down the entire columns you want.