How do you get formulas to adjust to new cells?

**Excelatexcel** · 09-29-2011, 07:55 AM

I have a formula comparing my two schedules (to see if there are any scheduling conflicts).

=COUNTIF($A$2:$A$83,$I2)

That's the formula. My question is, when I copy that formula to the next cell (and column) over to compare schedules in A and J, for instance, how do I get the formula to automatically adjust to the next cell over?

For example, when I copy and paste that formula the normal way, say I copy it, then I highlight the next 20 cells to the right of it to copy it into all of them, what I get is just the same exact formula over and over again in those cells.

I just get this over and over.

=COUNTIF($A$2:$A$83,$I2)
=COUNTIF($A$2:$A$83,$I2)
=COUNTIF($A$2:$A$83,$I2)
=COUNTIF($A$2:$A$83,$I2)
=COUNTIF($A$2:$A$83,$I2)

When what I want to automatically happen is...

=COUNTIF($A$2:$A$83,$I2)
=COUNTIF($A$2:$A$83,$J2)
=COUNTIF($A$2:$A$83,$K2)
=COUNTIF($A$2:$A$83,$L2)

and so on.

How do I get the last part of the formula, the $I2, to automatically adjust one column over for every column to the right I paste it in, while keeping the A's constant?

**Domski** · 09-29-2011, 08:00 AM

This will probably help to explain it: http://www.cpearson.com/excel/relative.aspx

Dom

**Excelatexcel** · 09-29-2011, 08:16 AM

Thanks Dom, but okay, so what do I change exactly? If I take out the $ sign in $I2, i dont think the formula will work at all. Will it? Tell me what exactly to change in my formula and how to do it please so there's no grey area.

**Domski** · 09-29-2011, 08:20 AM

This should do it:

=COUNTIF($A$2:$A$83,I2)

The $'s only specify whether your reference is absolute, relative or mixed. They have no bearing on how the actual function calculates.

Dom

**Excelatexcel** · 09-29-2011, 08:22 AM

And what about if the data always in adjacent columns? Like maybe I want my results, where im inputting the formulas, to just be every next column, but when I copy the formula one column to the right, I need the formula to actually adjust two columns in terms of the data, because there's a non-data column between the data.

As in, what you told me worked for what I described, but.....

Okay, say I have my schedules in column A, B, and C, and then I use this formula to compare them in the next three columns. Then pasting it from D to E would work. But what if Im pasting it from D to E, but I want the formula to switch from A to C? Like what if instead of 3 schedules in A, B, C, I had one in A, but something else in B, and then again in C?

What if i need the formula to switch two columns and not just one every time I paste? How do I do that?

**Whizbang** · 09-29-2011, 08:38 AM

If your data skips a column, but your formulas are only copied over one column, then you will need to manually adjust your formulas. Autofill cannot predict or read your mind. If you copy your formula one column over, it can adjust your formula for that column shift. But it cannot predict that, in this one case, you want it to actually adjust by two columns.

You could probably achieve the desired result with an Index/Match combination, and maybe an Indirect or something thrown in, but I would recommend first understanding relative and fixed references before trying to get too complex with your formulas.