How do I click and drag a formula changing only 1 value?

I am using this formula:
=IF($K$155,$C172,$A$164)
and would like to drag it along a row only changing the $C row value. So the next cell should read:
=IF($K$155,$C173,$A$164)
and so forth, but when I click and drag it only copies the formula with out making the change. What should I be doing differently? Thanks

Given you're transposing the reference I would suggest:

Please Login or Register  to view this content.

You could also use OFFSET though Volatile

Please Login or Register  to view this content.

NOTES:

-- change references to A1 to be the first cell in which the formula resides (ensure you keep the $ before the first cell reference)
-- modify 172 in the first suggestion to be the row number of the first value from Column C you wish to reference

Hi 9ball, and welcome to the forum.

If Scott wanted to drag the cell downward, then yes, it would automatically update the row number as required. However, his intention is to drag to the right and have the row number increment - which Excel won't do no matter how finely tuned your clicking finger is. DO's formulas should do the trick, though.

Apologies - passed quickly over "drag it along a row".

Thanks for the correction - And I concur, DO's solution works in this case.

I'd suggest uploading a sample of your workbook, in that case.

The principle of the formula works, you just need to adjust it to meet your needs. Based on your uploaded spreadsheet, try:

=IF($K$170,INDEX($B:$B,170+(COLUMNS($A1:A1)-1)),$A$169)

The columns function counts the number of columns in the range specified.

=COLUMNS($A$1:A1) will return 1, since there is one column in the range.

=COLUMNS($A$1:D1) will return 4, since there are four.

The INDEX function specifies a range to, well, index. The first cell in the range has an index of 1, the second cell an index of 2, and the 170th cell has an index of 170. So in the range $B:$B, Excel assumes you mean B1:B65536 (or higher in Excel 2007+).

=INDEX($B:$B,170) will return the value from the 170th cell in $B:$B. Because you're dragging the formula one column to the right every time, we can use that fact to increment the "170" in the formula by 1 each time.

Rather than using "170+COLUMNS($A$1:A1)-1", we could also have used "169+COLUMNS($A$1:A1)". Using 170, though, seems to make it easier to understand that you're starting in row 170.

=INDEX($B:$B,170+COLUMNS($A$1:A1)-1)
=INDEX($B:$B,170+1-1)
=INDEX($B:$B,170)
=value in cell B170

=INDEX($B:$B,170+COLUMNS($A$1:B1)-1)
=INDEX($B:$B,170+2-1)
=INDEX($B:$B,171)
=value of cell B171

Hopefully that helps you understand it better.