Dynamic Data Validation

[Cooki]

Hi All is it possible to do a dynamic validation list using data from multiple sheets.

I have a front page and 4 identical sheets, just with different sheets names.

I need to be able to have the list of all names in B3:B75 from each sheet in 1 list, also needs to ignore any blanks.

Working on the below but cant get it to work, excel tells me it can only be from 1 sheet.

=OFFSET(Commish_Q1!$B$3,0,0,COUNTA(Commish_Q1!B3:B75),COUNTA(Commish_Q2!B3:B75),COUNTA(Commish_Q3!B3:B75),COUNTA(Commish_Q4!B3:B75)))))

Any help would be greatly appriciated

 

[RasGhul]

Hi Cooki,

Here's a sample sheet, it dynamically updates the names list from 4 sheets into the DV tab.

Note that you will need to expand the range of the formula, if the sheets lists move beyond row 75.



https://www.dropbox.com/s/bhanjtzvlyfgqyq/Dynamic Data Validation_Cooki.xlsx?dl=0

 

[Cooki]

I will check this out, but my laptop has packed it, so fixing atm.

Thank you for replying