Forum Discussion
Data Validation
You could use the range to the 'HiddenSheet' and then drop that last item (assuming you keep the 'HiddenSheet' at the end
edit: I updated the table formula to do this and included the list of Clients so I could update the Validation formula to NOT use the SHEET() function but rather do a lookup based on the Client name:
=INDEX(DROP(RateList,1),,XMATCH(B1,TAKE(RateList,1)))
so now row 1 of the RateList range is the client name. See attached.
Hey! I think you are onto exactly what im looking for. However, when I add the formula in to my sheet, change the tables to the correct tables and ranges and then shange the sheet ranges from sheet 1 (has a different name since all sheets are always changing) to HIDDEN TABLES (the sheet at the end that will act in place of the end sheet we had on your example) and it doenst work. it keeps adding [HIDDEN TABLES] in front of HIDDEN TABLES in the formula. like this =IFERROR(DROP(REDUCE("",DROP(HSTACK('Sheet1:[HIDDEN TABLES]HIDDEN TABLES'!B2),,-1),LAMBDA(p,q,HSTACK(p,VSTACK(q,FILTER(ChargesTable[Charge Title],ChargesTable[Client]=q,""))))),,1),"")
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),"")