Forum Discussion
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 / up to date. This was created when there was only one potential client and I set it up in a way where the table on HIDDEN PAGES (sheet) is filtered into an adjacent filter function a few columns to the right of the table and that spill range is then named and used to make a data validation list on sheet 1. this is fin if there is only ever going to be one client because when I copy the first page to make more sheets (has to be done this way) the list on sheet 2 is still based off of the entry on sheet 1. See example spreadheet.
Is there a way to make the data validation list dynamic for new sheets that are created by copying the sheet forward so that each new drop down list (column E on the sheet pages) are based on column B of those individual sheets?
9 Replies
Hey Hans, Thanks for the reply. The hidden column idea is an option that I explored (I should have explained that also), however, in the actual spreadsheet there are multiple filtered spill ranges and multiple lists. I will resort to that if I cannot figure out a way to build the formula into the data validation formula bar but ideally I stay away from that since I would need 10 to 15 hidden columns per page and part of me worries about hidden ranges becoming an issue in the future. If there isnt a simple solution then I will definitely go that route though. Thanks for the reply!
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?
