Cell Autofilling Issue

Hey guys,

I'm not sure if this qualifies as a basic Excel question or not, but I'm having an incredible hard time getting the autofill to follow the pattern that I set for it.

In the first three cells of the series I have the following:

=IF((COUNTIF(F3:F12,"Hello!"))>=1,"Hello!","Not Hello!")
=IF((COUNTIF(F13:F22,"Hello!"))>=1,"Hello!","Not Hello!")
=IF((COUNTIF(F23:F32,"Hello!"))>=1,"Hello!","Not Hello!")

As scene here, incrementing the cell range by ten every time is my goal. Yet when I select the series and drag to fill down the row my desired length, instead of catching the pattern, it instead increments the next cells in the series by 1 instead of 10, like follows:

=IF((COUNTIF(F24:F33,"Hello!"))>=1,"Hello!","Not Hello!")
=IF((COUNTIF(F25:F34,"Hello!"))>=1,"Hello!","Not Hello!")
=IF((COUNTIF(F26:F35,"Hello!"))>=1,"Hello!","Not Hello!")

Can someone please tell me what I'm doing wrong and if there is something else I need to do in order to get the autofill to follow my pattern and increment by 10 instead of 1.

Thank you and all the best,

ExcelRogue

I'm a bit confused with what you wrote here Martin. Did you leave a piece of the code out?

Originally Posted by martindwilson

=IF(COUNTIF(OFFSET($F$3,ROWS($A$1:A1)*10-10,,10),"hello!"),"hello!","not hello!")

Benshiryo,

Attached is some example code. As you can see in mine, if you click the trace precedent button, mine points to a range of cells. Your solution points to C1, an empty cell. Any ideas?

Originally Posted by excelrogue

Benshiryo,

I tried what you said, but now I get this error saying that I'm pointing to an empty cell. I pressed the trace precedents button, and it reveals that I am indeed pointing to an empty cell. Could this be because I didn't start at row 1? I started from row 3.

excelrogue

Hmm. Tried that but still seem to be getting the formula refers to empty cell error. Literally that formula you wrote points to D1, which as you can see in the excel document, is empty. Any ideas?

Originally Posted by benishiryo

your previous formula was looking at column F. now in your eg, i suppose you want to look at column C & count if there is 1 or more "Hello!". so try:
=IF((COUNTIF(INDIRECT("C"&3+((ROWS($D$1:D1)-1)*10)&":C"&12+((ROWS($D$1:D1)-1)*10)),"Hello!"))>=1,"Hello!","Not Hello!")

this is equivalent of putting:
=IF((COUNTIF(C3:C12,"Hello!"))>=1,"Hello!","Not Hello!")

but when dragged down, it increases the range by 10 rows. so it'll be:
=IF((COUNTIF(C13:C22,"Hello!"))>=1,"Hello!","Not Hello!")

My apologies. Thank you for taking the time.