Automation - if blank move forward

[N0t Y0urs]

I start with I apologise if this sounds confusing.

I am working with Excel 365 but also needing it to be compatible with Google sheets.

My workbook has 6 sheets in it a dashboard which I am pretty much fine with then 5 other sheets.

Each sheet has a range of A1:FI625. The data is broken down into 21 subgroups. The first group is a simple total of the following 20.

What I am wanting to achieve is an automatic value return if a cell is not blank. For example M12 = 250 then I want W1 = M12, then if M17 = 500 then I want AD1 = M17, and if W17 = 250 then I want AK1 = W17

I want this to repeat 99 times. If I take M12 it is returning a value based on P11 and this populates the next subgroup. In addition to just moving 5 rows each time in the first group I want each subsequent subgroup to help populate the remaining 99.

I am doing this manually at the moment but making changes to % values changes the results which could result in M12 not meeting the threshold to report a value.

Thanks in advance

 

[N0t Y0urs]

I have uploaded an image to help with my explanation and I have colour coded the way it populates.

 

[N0t Y0urs]

So I am continuing my experiment and I’m trying to create the logic of an if and blank formula for this. Am I on the right path?

 

[N0t Y0urs]

I’m bumping this back up hopefully I will get some ideas.

I have 20 data sets, each have 6 columns. Every 5 rows potentially will start a new data set this is what I want a formula for