Scotster
Board Regular
- Joined
- May 29, 2017
- Messages
- 52
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
I've no idea how to describe what I'm looking for in a heading, so hopefully the below explains it a little better. Is there a more elegant way of calculating the below.
The formula I have in D6 is as follows:
It's copied from D4 to D16, where D4 and D5 will REF error for the "Net" true calculation, but doesn't affect anything.
My aim is to replace the "-10" and "5" with a formula from another sheet. The reason for the convoluted method is there can be multiple Suppliers, so some parts cover 3 lines or more. The offset only allows for 6 lines at the maximum so I was hoping there would be a better option.
What I want is for the demand line to use one calculation, the net line to use another calculation and the remaining supply lines to use a 3rd calculation. There are 10000s of rows, so creating one formula to copy down the entire sheet I feel is beneficial, rather than using VBA to determine the individual formulae per row. The difficulty with the "Net" calculation, is that it's simply a sum of the rows above it..... up to and including the "Demand" row. With the "Demand" row varying in distance I've used the match to find the correct offset.
As always, any help gratefully appreciated
The formula I have in D6 is as follows:
Code:
=IF($C6="Net",SUM(OFFSET($D6,MATCH(1,($C1:$C6="Demand")*($B1:$B6=$B6)*($A1:$A6=$A6),0)-6,0):$D5),IF($C6="Demand",-10,5))It's copied from D4 to D16, where D4 and D5 will REF error for the "Net" true calculation, but doesn't affect anything.
My aim is to replace the "-10" and "5" with a formula from another sheet. The reason for the convoluted method is there can be multiple Suppliers, so some parts cover 3 lines or more. The offset only allows for 6 lines at the maximum so I was hoping there would be a better option.
What I want is for the demand line to use one calculation, the net line to use another calculation and the remaining supply lines to use a 3rd calculation. There are 10000s of rows, so creating one formula to copy down the entire sheet I feel is beneficial, rather than using VBA to determine the individual formulae per row. The difficulty with the "Net" calculation, is that it's simply a sum of the rows above it..... up to and including the "Demand" row. With the "Demand" row varying in distance I've used the match to find the correct offset.
As always, any help gratefully appreciated
