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.

Resources