Forum Discussion
Data Validation
Perhaps like in the attached version. You can hide column M on Sheet1 and Sheet1 (2) if you like.
Here is an option:
I create a table on the hidden sheet and then the data validation uses an INDEX of that table based on SHEET()
See attached.
Thanks for the input M_Tarler. is there a way to do this where i dont need the SheetN? or do I always need to have an identical extra sheet at the end of the workbook?
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),"")
