calculating file intervals from 300 to 300

[boboivan]

Hello experts!

Here is a difficult one!

I have a column O for inserting the number of document files that is registered in my workbook (ex: 3 files).
In column P a have a formula that calculates the interval between files (ex: from 1 to 3), which is:

Please Login or Register  to view this content.

In column Q I have a formula that calculates Folder's successively number if the files rich the maximum of 300 (ex: volum 1 for the first interval from 1 to 300, volum 2 for the next interval of 1 to 300, and so on):

Please Login or Register  to view this content.

The problem is that the formulas work only for the first interval. When the files gets to first 300 and is needed to recycle the hole process for the next interval of 300 files, which should be in volum 2, and so on.

I've attached the workbook.

Any ideas how can be solved?
Is there a formula for this, or is necessary a VBA code?

Thank you so much!

[samba_ravi]

see the attached file

[samba_ravi]

Ignore my earlier post
see this one

[boboivan]

Thx for the reply!

The results are faulty.
From the third row down the intervals are faulty.
In the third row should be 13 - 77 and not 10 - 77.
the forth should be 78 - 197 and not 66 - 197, and so on

[samba_ravi]

I observed that after my 2nd post
Please see my 3rd post

[boboivan]

Ignore my earlier post
see this one

Works like a charm!
Thank you so much sir!

[boboivan]

Ignore my earlier post
see this one

Works like a charm!
Thank you so much sir!

[boboivan]

I observed that after my 2nd post
Please see my 3rd post

I've noticed though a small problem.
If I add filters on my sheets, the values doesn't change to the new visible values.
Anything that can be done here?

[samba_ravi]

i did not get you

[boboivan]

i did not get you

My original worksheet has 13 columns. Usually I filter information from other columns and I really need the values in column P and Q to change (to recalculate) according to the visible rows.

Please, see attached!

[samba_ravi]

is it ok if I create one helper column

[boboivan]

That shouldn't be a problem!

[samba_ravi]

see the attached file

[boboivan]

You are truly a genius!

Thank you so much sir!

[boboivan]

You are truly a genius!

Thank you so much sir!

[boboivan]

see the attached file

I've sent you a private message regarding this thread.
Did you get it, 'cause I can't see it in my private messages>sent items section?

Sorry for disturbing you!

[samba_ravi]

I received your message
Q2=SUBTOTAL(3,R2:R2)*(INT((SUBTOTAL(9,O$2:O2)+MAX(0,(MAX(Q$1:Q1)-1)*300-SUMIFS(O$1:O1,Q$1:Q1,"<"&MAX(Q$1:Q1),Q$1:Q1,">"&0)))/300)+1)
TRY THIS AND COPY TOWARDS DOWN