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

**scottghansen79** · 10-25-2010, 12:12 PM

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

**DonkeyOte** · 10-25-2010, 02:27 PM

Given you're transposing the reference I would suggest:

=IF($K$155,INDEX($C:$C,172+(COLUMNS($A1:A1)-1)),$A$164)

You could also use OFFSET though Volatile

=IF($K$155,OFFSET($C$172,COLUMNS($A1:A1)-1,0),$A$164)

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

**9ballpimp** · 10-25-2010, 05:45 PM

If you're dragging it correctly, then it will automatically renumber that reference.

Copy it, and paste it down. See if that changes it.

Troy

**Paul** · 10-25-2010, 05:49 PM

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.

**9ballpimp** · 10-25-2010, 06:01 PM

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

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

**scottghansen79** · 10-25-2010, 06:30 PM

DO's suggestion did not work for me. I have a set of data that shows the every month through 2014 in one column, then the next shows how many work days in each month, then the next column shows how many mondays, next is tuesdays and so on. So my formula fits in with a combo box that allows the user to pick which days a week a client will be using our services. Maybe a vlookup code instead.

**Paul** · 10-25-2010, 06:38 PM

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

**scottghansen79** · 10-25-2010, 06:53 PM

I have an older version of the spreed sheet at home but it will still work just different formula cells. The data starts at A170 and the formula should go in L170. I already went through and entered the continuing formulas by hand to do what I wanted, but I am interested in learning how to do it an easier way.

**Paul** · 10-25-2010, 07:02 PM

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)

**scottghansen79** · 10-25-2010, 07:07 PM

That works, but I don't understand it. I don't understand the Columns. When I drag it over it works and each cell only changes the (columns($a1:a1) to (columns($A1:B1) and so forth. What does the columns function do?

**Paul** · 10-25-2010, 07:29 PM

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.

**scottghansen79** · 10-25-2010, 07:34 PM

Thanks for the help Paul. I will have to wait until I get back to work tomorrow and see if I can apply this info into my current project. Thanks again.