I would like to select only a repetitive set of column from a structured table to apply data validation or conditional formatting.
I tried to create named range with a formula but without success. Here is where I am at :
=INDIRECT(LET(LetterHeader;LEFT(Zn_Pln_1an;1);Val_Ref_NameTblPln&TEXTJOIN(";"&Val_Ref_NameTblPln;TRUE;TEXT(FILTER(Zn_Pln_1year;(LetterHeader<>"H")*(LettreEnTete<>"E"));"\[dd/mm/yyyy\]"))))
Which return, without the INDIRECT
, exactly what I wanted :
Tbl_Planning[01/01/2022];Tbl_Planning[02/01/2022];Tbl_Planning[03/01/2022];Tbl_Planning[04/01/2022];Tbl_Planning[05/01/2022];Tbl_Planning[06/01/2022];Tbl_Planning[07/01/2022];Tbl_Planning[08/01/2022];Tbl_Planning[09/01/2022];Tbl_Planning[10/01/2022];Tbl_Planning[11/01/2022];Tbl_Planning[12/01/2022];Tbl_Planning[13/01/2022];Tbl_Planning[14/01/2022];Tbl_Planning[15/01/2022];Tbl_Planning[16/01/2022];Tbl_Planning[17/01/2022];Tbl_Planning[18/01/2022];Tbl_Planning[19/01/2022];Tbl_Planning[20/01/2022];Tbl_Planning[21/01/2022];Tbl_Planning[22/01/2022];Tbl_Planning[23/01/2022];Tbl_Planning[24/01/2022];Tbl_Planning[25/01/2022];Tbl_Planning[26/01/2022];Tbl_Planning[27/01/2022];Tbl_Planning[28/01/2022];Tbl_Planning[29/01/2022];Tbl_Planning[30/01/2022];Tbl_Planning[31/01/2022]
With the INDIRECT
I get #VALUE
since I try to show non contiguous selection in one cell (I hink that is what generate the error).
I would like to refrain from applying the data validation to the whole table and make it dynamic relative to the headers since that would generate warnings in the columns not expecting the validation.
I am open to all suggestion as how to select all those columns with the specific goal of applying a data validation to them. (In the real version the table is about a thousand column wide and can sometimes vary). Thank you