Dragging cell's formula changes BOTH referenced cells - don't want that!

[markinva]

Sorry for the long post!

Please see the attached Word doc - it has a table that I copied from a
spreadsheet I'm working on. I included each cell's formula to help
explain what I'm trying to do.

As you can tell, I'm creating a kind of fuel calculator/comparison
chart. I want to be able to compare the amount spent over a year for a
couple of vehicles with a certain mpg rating. There's more to the
chart, but this information is sufficient for my question/problem.

I'm including fuel prices from $1.50 to $4.00 (man, can you believe
prices these days?! ). That's two hundred and fifty rows of fuel
prices - per car. Obviously I'm trying to find the quickest way to get
the formulas in the corresponding columns.

What I tried to do was drag, for instance (in the attached table) cell
B10 down. To make the chart work, the first cell in the formula must
change to reference the fuel price in the next row, but the reference
to the car's gallons used must stay the same. But what actually happens
when I drag the cell is *both* parts of the formula change to reference
the next cell down from each cell refernced in the formula. So dragging
from B10 down, it changes to "=(A11*C4) when I want it to read
"=(A11*C3)".

Is everyone thoroughly confused? Good! Now for the question:

Is there anyway to automate or otherwise speed up the formula entry
instead of manually entering all 250 - sorry *500* formulas (for the
two cars' colums)?!

Edit: I have never ventured in to using any special commands (vlookup,
pmt, even SUM) but I'll learn...

Thanks for anyone's help!

Mark


+-------------------------------------------------------------------+
|Filename: Fuel calculator example.doc |
|Download: http://www.excelforum.com/attachment.php?postid=3701 |
+-------------------------------------------------------------------+

 

[Cougar548]

If I read this right, then all you need to do is make the formul
absolute. You can do this by simply adding dollar signs to the cell
This is what your formula should look like in B8:

=(A8*$C$3)

Now as for a quick way to automate this and apply it to the entire lis
(again, if I read this right), if you double click on the bottom righ
of the active cell with the formula you want to copy, you will see tha
it will automatically apply that formula all the way down your shee
until it runs out of data. Pretty cool, huh?!

Hope that helps!
:

 

[markinva]

Beautiful!! Worked like a charm!!

Thanks.

Mark :

 

[RocketDude]

Mark,

The attachment didn't show up, so this may or may not solve your problem but
here goes:

If in your example, cell C3 should always point to the same cell, then make
it an absolute reference -- $C$3. Then you can drag the formula and it won't
change.

Hope this helps,