PDA

View Full Version : [SOLVED] Get an automatic sequence in a formula



ioncila
01-05-2014, 10:18 AM
Hi
I'm getting trouble with this:

I have this formula in a table with a range of rows

In cells N248 to 253 =IF(OR(N237=0,N237=LARGE('Folha4 (2)'!$AA$5:$AA$94,1)),0,SMALL('Folha4 (2)'!$AA$27:$AA$94,COUNTIF('Folha4 (2)'!$AA$5:$AA$94,0)+1))

And I want to change the red part, in an automatic way, to save (many) time if doing that manually

In cells N259 to 264 =IF(OR(N248=0,N248=LARGE('Folha4 (2)'!$AA$5:$AA$94,1)),0,SMALL('Folha4 (2)'!$AA$28:$AA$94,COUNTIF('Folha4 (2)'!$AA$5:$AA$94,0)+1))
In cells N270 to 275 =IF(OR(N259=0,N259=LARGE('Folha4 (2)'!$AA$5:$AA$94,1)),0,SMALL('Folha4 (2)'!$AA$29:$AA$94,COUNTIF('Folha4 (2)'!$AA$5:$AA$94,0)+1))
and so on...

Can this be done?

Thanks in advance
Ioncila

Bob Phillips
01-05-2014, 10:48 AM
See if this works for you

=IF(OR(N247=0,N247=LARGE('Folha4 (2)'!$AA$5:$AA$94,1)),0,SMALL(OFFSET('Folha4 (2)'!$AA$27,INT((ROW()-247)/11),0):'Folha4 (2)'!$AA$94,COUNTIF('Folha4 (2)'!$AA$5:$AA$94,0)+1))

ioncila
01-05-2014, 12:01 PM
Thank you
It works great.

Ioncila