Absolute Offset ?

[johnny52]

Is there a way to move an absolute value cell reference,up or down in a worksheet column with an offset formula.....or some other method
not using a relative or moderate value...my worksheet is large and the table contents I want to count,keeps changing,skipping rows

In the example below...axy226 is the absolute.......it works fine till the range needs to be changed.... i want a new countif with a new absolute on the workheet without having to manually change the cell in the formula, when I copy it down,The calculation and formula will always be in the same column
so I was hoping to just copy it down....Hope I explained this clearly Thanks everybody.

=IF(C226=C227,COUNTIF($AXY$226:AXY238,$AYC229),COUNTIF($AXY$239:AXY245,$AYC229))

$AXY226 and AXY$226 won't work with

 

[Special-K99]

Not sure I've understood correctly.

You always want $AXY$226 to stay the same in the formula?

Maybe

INDIRECT("$AXY$226")

should do it.

 

[johnny52]

No,I'd like it to change when I skip a few rows within the same column......a new countif because the range will change.......$AXY226 works until i skip down a few rows on the worksheet.Guess that's why its absolute.Thought maybe a formula existed like $AXY$ +1

 

[Special-K99]

"No,I'd like it to change when I skip a few rows within the same column"

Ok can we have some examples

You're copying this formula down the column are you?

=IF(C226=C227,COUNTIF($AXY$226:AXY238,$AYC229),COUNTIF($AXY$239:AXY245,$AYC229))
So what do you want it to say on the next few rows, ie, what should it change to?

 

[johnny52]

Yes copying it down the same column possibly skipping 8 rows then again possibly 20 rows etc...

so the new formula would need a new absolute cell to replace $AXY$226...............I know I can just hit the F4 key but I would have to do that numerous times.....I appreciate you helping out

=IF(C234=C235,COUNTIF($AXY$234:AXY241,$AYC237)....I've been trying to use an if statement to adjust for the new cell,so far no luck

 

[Special-K99]

So in the first row let's say A1 you want

=IF(C226=C227,COUNTIF($AXY$226:AXY238,$AYC229),COUNTIF($AXY$239:AXY245,$AYC229))

in A2 you want

=IF(C234=C235,COUNTIF($AXY$234:AXY241,$AYC237)... everything increased by 8 ?

Is that correct?

 

[johnny52]

well for that example in creased by 8 I have some formulas I'm been trying out and they somewhat work

=IF(C156=C155,0,COUNTIF($AXY156:AXY163,AYC156))

=IF(C157=C158,COUNTIF(OFFSET($AXY157,-1,):AXY164,AYC157))

=IF(C158=C159,COUNTIF(OFFSET($AXY158,-2,):AXY165,AYC158))

it works when the data range changes...the offset has to decrease in each row to read the original correct absolute value

 

[Special-K99]

Without the proper data this is pretty much speculation and guesswork.

 

[johnny52]

I understand completely,but luckily I have figured out a quick fix,at least for my worksheet

=IF(C2=C3,COUNTIF(OFFSET($AXY2,AYM2,):AXY9,AYC2))

the offset worked and putting a negative running count in another column AYM (-1,-2 etc....)

I was able to copy it down and adjust the absolute value.............I appreciate all the help THANKS !