Forum Discussion

Jn12345's avatar
Jn12345
Brass Contributor
Apr 02, 2025

Data Validation

Hello All, I have a data validation question. I created a costing spreadsheet that can pull data from a master spreadsheet so that multiple copies of the costing spreadsheet can always be current / ...
excel
m_tarler's avatar
m_tarler
Bronze Contributor
Apr 02, 2025

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.

Jn12345's avatar
Jn12345
Brass Contributor
Apr 02, 2025

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?

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Apr 02, 2025

    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.

    • Jn12345's avatar
      Jn12345
      Brass Contributor
      Apr 02, 2025

      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),"")

      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        Apr 02, 2025

        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.

Resources