Ok ,so I am hoping I can do this without VBA, if it needs VBA I'll have another think on it and can probably do it, but would prefer to use the built in functions if possible.
So first my data table:
What I want to do is look for the maximum QTY value, that has no WORKER assigned to it, and then return either the value of corresponding cell in the UNIQUE column, or else at least the address of the max value that I can then use to find the UNIQUE cell myself.Please Login or Register to view this content.
I've tried using array formulae to find the max value that doesn't have a worker, and that works well, but I can't see how to get the unique from that.
Similarly using the array formula I can get the max value using an array formula, and then use a Match function to get the address of a cell with that value in the QTY column, but the match function can pickup a cell with the correct value, but with a worker assigned (as it's just matching value, not the conditions used in the max formula).
The above example should return either, $B$5, or $A$5, or "8765", I would be happy with any of those to indicate which row has the max qty with no worker.
In the situation where there are 2 possible cells, (ie. if row 3 had no worker there would be two '58's available), then just returning the first one is fine.
Any ideas?
Thanks
Bookmarks