Breakdown formula not working correctly

Michaela Kamenska

Hi Paul,

I've tested the formula and we have an interesting issue. Now it works on some but not all (actually only works on those 2 rows and only for Jan-21

I tried also different rows and again, I'm getting this - most errors invalid data type, first row with the formula shown gives me #INVALID OPERATION:

More up within the thread I posted this - the formula worked on first row of month for EU only but when I tried dragfill, it changed the [global lead] changed to [Jan-21] in Feb-21 column, then to [Feb-21] in Mar-21 Column etc:

Have you got any possible formula in mind we could try apply? and start from scratch?

Paul Newcome

If you have something that is working on all rows and you just need to dragfill, then you should be able to lock in row/column references using the $.

$ before the column name to lock in the column and before the row number to lock in the row.

$[Lead Start-Up Date]@row

[Jan-21]$1

Michaela Kamenska

I dont have anything.. I thought my original formula in this thread would work but it doesn't. If you could help me at least figure out how to extract either one date or another from the source sheet into a startup column, that would be great. In the source sheet I have "lead start-up date" and "regional start-up date" and I want to pull only the one that is the [global lead]@row in my sheets. I've gone through different queries on here but I can't seem to find and index/match or whatnot formula that would be pulling from the right column. The problem is that the regional startup can be blank, meaning that the regional starts at the same time as lead.

Paul Newcome

My suggestion would be to pull both into the target sheet. Then use a 3rd helper column (can be hidden) with a formula to choose between the two date cells which one to pull. Then in the breakdown formulas you would reference this helper column.

Something like...

=IF(OR([Global Lead]@row = "EU", [Regional Start-Up Date]@row = ""), [Lead Start-Up Date]@row, [Regional Start-Up Date]@row)