copying formula to other cells problem

[avd2007]

I'm developing a pricing matrix for a client. In column A the length of their final product is listed in inches. In Row 1 the width of the final product is listed in inches. There is a very complex formula for multiplying the different values against various raw material costs, and then adding a mark-up to the whole thing. The matrix looks a bit like this:

<pre>
A B C D
(1) 22 24 26
(2) 12
(3) 14
(4) 17
(5) 20
</pre>

So the formula for my first cell, for calculating the price of a product that is 12x22, is something like (B1/12)+(A2/12).

Then to copy it into the next cell down, I want to get a formula of (B1/12)+(A3/12). I want B1 to stay the same for that entire column, and only the value from the A column to increment to the next row. But the formula is also being incremented to B2, B3, etc.

Is there a simple solution to this? I have a couple hundred rows and 30 some columns so editing the code and handpasting cell by cell does not seem efficient.

Thanks!

 

[danzon]

=(b$1/12)+(a2/12)

 

[VoG]

Try

=B$1/12+A3/12

 

[T. Valko]

I'm developing a pricing matrix for a client. In column A the length of their final product is listed in inches. In Row 1 the width of the final product is listed in inches. There is a very complex formula for multiplying the different values against various raw material costs, and then adding a mark-up to the whole thing. The matrix looks a bit like this:




A B C D
(1) 22 24 26
(2) 12
(3) 14
(4) 17
(5) 20

</PRE>

So the formula for my first cell, for calculating the price of a product that is 12x22, is something like (B1/12)+(A2/12).

Then to copy it into the next cell down, I want to get a formula of (B1/12)+(A3/12). I want B1 to stay the same for that entire column, and only the value from the A column to increment to the next row. But the formula is also being incremented to B2, B3, etc.

Is there a simple solution to this? I have a couple hundred rows and 30 some columns so editing the code and handpasting cell by cell does not seem efficient.

Thanks!

Try it like this...

=(B$1/12)+(A2/12)

The dollar sign $ will keep the row reference in B1 from changing.

 

[Norie]

I thiknk you just need to anchor the rows for B and the column for A.

You can do that by adding $.

Assuming the 12 is constant perhaps something like this.

=(B$1+$A2)/12

 

[avd2007]

Thanks, that works great! Especially the tip about how to anchor both the row AND the column. All my values are filled in and I spot-checked to validate -- they are all correct.