Forum Discussion
Another Dynamic Spilled Array challenge - Multiply 2 spilled arrays while matching criteria
James_Buist From what I can tell, the only change needed is to multiply by the table in blue (H10:L16) instead of the column in red (D10:D16). See attached...
Sorry. Huge apologies. I messed up in my explanation. Too late at night I think and brain fried. Thanks for solving this. I think I could have got a solution, maybe not as clean as yours so this is still very useful. But what I missed out was the most crucial part. The table of monthly amounts is already completed. Its not taken from the left hand column but generated from input tables with stop and start dates and manual override price changes. You actually helped me solve that in the first place!! So what I need to do is to multiply the two tables together. SO ignore the column with monthly prices (In red in the attached). It's a case of multiplying the green and blue tables together with the condition of matching the Entity and whether fixed or per headcount.
James_Buist From what I can tell, the only change needed is to multiply by the table in blue (H10:L16) instead of the column in red (D10:D16). See attached...
- As usual, brilliant. Works a treat. I wrapped the Index in an Ifna and that cleaned up the errors.
I think I haven't quite got my head around forcing the array to spill. Sometimes it does and sometimes it doesn't and I don't totally see what causes that.
It The sequence columns forces it to spill across all the columns and also gives the column number - 2 in one, clever. Then the if(C10:c16="Fixed" causes it to spill down the rows.. That is crafty because actually it's just an index match. As that creates an array of the dimensions of the one with the amounts in it - the blue - one and the staff numbers are now all in the right rows, I get why the simple multiplication works. I hadn't envisaged getting the two tables the same size. **bleep** clever. I'm stretching my capabilities here but have a number of other challenges ahead. You have been amazing;y helpful and I hope to be able to resolve many more with, hopefully, increased knowledge and understanding.