Multiple formulas and streamlining

[N0t Y0urs]

Hi
I apologise in advance if this is not clear but I’ve tried to refine my thoughts.

In this link is a modified (smaller) version of a file I’m working on - Help Please.xlsx

I have a page called explanation trying to highlight what I am wanting to achieve but I will go over it here as well.

Firstly, if there is a more efficient way to do this I am all ears.

Basically, I will have 100 different data sets, with each set having 4 columns in it and at least 500 rows of data. If I have a value in FWD I would like a new data set to be created and the value of the new set to be $25. Therefore if it says $100 it would create 4 new data sets on the next row, with a maximum of 99 new sets after the original one.

With each new data set the value in the TP column is being corrected from the input sheet and then RB column is a formula.

The DB column is my next confusing point as when the RB columns is above $50k I want a formula that will show the amount above $50k so every 5 rows it goes back to $50. I kept getting circular errors here.

So this brings me to my summary requirements. By grouping my data sets I can total what’s going on at a specific point in time (data range of 5,10,15 etc) then I can also total it. So I am not sure if I need go overboard and do the formula based of where the first 20 are located then do the same with the next 20 and so on or if there’s a hack to the formula to reference the data set numbers.

And while I think this is a work in progress is there an easy way of auto filling a formula when I am skipping every 4 lines like on my totals page?

Thank you in advance for any assistance and direction I receive

 

[N0t Y0urs]

I’m still figuring this out so will re engage if I need assistance

 

[Fluff]

Please do not mark a post that doesn't contain a solution as the solution.