Hi there, I tried solving a question people asked me, but can't figure it out with the modern spill functions.
I remade some of the format in a anonymous way in the Excelsheet (two tabs, one with two tables with data and the sheet where the solution has to come).
I hope somebody can give me some clues or direction how to solve this problem using the modern 365 functions.
thanks in advance!
Frans
Try this in B43
=IF(INDIRECT("Table_Matrix["&Person&"]")="X",XLOOKUP(SubNrs,Table_Tasks[subnr],Table_Tasks[task],""),"")
Note I made B41 and A43:A51 named ranges ("Person" and "SubNrs" respectively)
Changing the Person via the data validation in B41 will alter the tasks assigned to that person.
I hope this is what you are after.
See attached with some small changes in the orange shaded cells. Basically the same as Charles' solution but with a formula for the subnrs in A43 and a formula in D43 that avoids INDIRECT.
Much better, Riny!
INDIRECT should be avoided whenever possible due to it volatility.
Thanks very much Charles and Riny for your contributions! (I see one extra from Charles is awaiting moderation, but these two are also very nice).
I had a quick view and saw you solved an import part of my problem, so the information of the persons comes in the right way.
Only thing left is that it is based on the complete list in column A. So if for instance 1.1 and 1.2 are valid and 1.3 and 2.1 not, they keep blank.
Is there a way only to see the parts that are valid? Maybe without the given list in column A?
Frans
