Forum Discussion
Data Validation
so that looks like a filename reference and I have no idea why it is inserting that. Instead of typing it try selecting that range. select the whole range of sheets (use shift click to select the range) and then click on cell B1. See if that works.
Starting to get somewhere, however, no spill range is produced. Im completely unfamiliar with the reduce function so I am probably getting something wrong when I pump this in to my workbook. however another difference from my report to the one you have worked on is that my B1 range in this form is on B2 in the real form. I tried to just type in B2 where you had B1 but still doesnt work.
ACTUALLY, because we changed the Data Validation to basically look-up in table the order of the table columns don't matter so try this instead:
=IFERROR(DROP(REDUCE("",UNIQUE(Table1[Client]),LAMBDA(p,q,HSTACK(p,VSTACK(q,UNIQUE(FILTER(Table1[Rate Type_A],Table1[Client]=q,"")))))),,1),"")