Macro to Fix Auto-Pattern?

Hi,

The spreadsheet I'm working on is kinda hard to describe, but I'll try.

It's a finance calculator of sorts that I need done, and it shows me the total cost of various products. Each of these products require different components, and numbers of components, and also these components all cost different prices. So what I have is the following spreadsheet setup:

Please Login or Register  to view this content.

So all I have to do is enter the amount of the component needed and my spreadsheet (this part I've done) multiples the 'Component' by the 'Number Needed.'

Now the thing is that there are quite a few different products, so in the side I've created a universal price list, that shows the prices of all my products. So to get the price of each product I've further created these columns:

Please Login or Register  to view this content.

And what I want to do is make it multiple the Number Needed * Cost of Component and then be able to use the auto formula to go down the page to autofill these values (since I have about 100, so that would take AGES manually).

So how could I do this? The pseudo code for this patter autofil would be:

Column AA = R[ROW NUMBER]
Column AB = S[ROW NUMBER] * AO14
Column AC = T[ROW NUMBER] * AO15
Column AD = U[ROW NUMBER] * AO16
Column AE = V[ROW NUMBER] * AO17
Column AF = W[ROW NUMBER] * AO18
Column AG = X[ROW NUMBER] * AO19
Column AH = Y[ROW NUMBER] * AO20


And then the pattern as it auto fills would be:
Column AA = R[ROW NUMBER + 1] * AO14
Column AB = S[ROW NUMBER + 1] * AO15


But Excel doesn't put that down as it's auto fill. Instead it decides to do:
[ROW NUMBER + 1] * AO17

ALL the way down... which is wrong. How could I fix this?

Thanks (hope I explained my problem clearly, if not please ask I'll try to clarify
-Rudey

Its Relative vs Absolute addresses. You'll want to read up on it. It's one of most basic strengths in Excel.
If you look up "Relative Address" in the help file, they can probably explain it better than I can.

The $ in from of the row # tells Excel you want that specific row (absolute row). Without the $ tells Excel you want the row that is x rows above or below the cell (relative row).
Works the same for the Columns. Put a $ in front of the Column letter and it refers to a specific column. Without the $, it refers to a column that is x columns to the left or right.



When you entered the formula into cell AB4: = S4*AO14 you were telling Excel you wanted "the cell 9 columns to the left (S4) * the cell 13 columns to the right and 10 rows down (AO14)".
So when you pasted into AB7 it put in a formula: "the cell 9 columns to the left (S7) * the cell 13 columns to the right and 10 rows down (AO17)" (result S7*AO17)

With you entered the formula into cell AB4: = S4*AO$14 you were telling Excel you wanted a formula that refered to "the cell 9 columns to the (S4) * the cell 13 columns to the right and ROW 14".
So when you pasted into AB7 it put in a formula "the cell 9 columns to the left (S7) * the cell 13 columns to the right and Row 14 (AO$14)" (result S7*AO$14)


Does that explain things?