In the attached workbook you will see quite a few worksheets. The validations are on the Yellow Tabbed sheet named "PSM-04-03A" in cells A4 and cells C4. The A4 validation populates a list of the active "DATA" worksheets (i.e. not WS "PHAR Home" or "PSM-04-03A". The C4 validation is a dependent validation which then populates a list the "ColumnA" items from the worksheet chosen in A4. I have determined that because the structure of the WB is going to change I would combine all these "DATA" sheets and just add a new column to account for the current WS Name. So essentially what I am doing is combining all the "DATA" sheets and adding a new column in which the original sheet name will be placed. The issue here is I will then need the A4 value to populate based upon options in that column not based on WS Names. I am uncertain of how to make this adjustments.

Lets use an example: Say "Sheet1" and "Sheet2" are the report sheets and "Sheet3"+ are all data sheets. I combine all further sheets into "Sheet3" and make a new columnB for the old WS Names. Say there were 10 items in "Sheet3", 8 items in "Sheet4", 6 items in "Sheet5" before I combined them in "Sheet3". I add them all to "Sheet3" and designate their origination. So the first ten items in columnB would say "Sheet3", the next 8 would say "SHeet4", and the next 6 would say "Sheet5". The issue then is because the "Sheet2" validation in cell A4 was based on WS names it doesn't function anymore. The goal now would be for it to be based on the ColumnB value in "Sheet3". Where it would provide "Sheet3", "Sheet4", "Sheet5" where then the C4 validation would filter based upon the column B value.

See attached WB for current functions:

(PSM-04-03) PHAR Tracking v2.xlsm