Combining Data Unsure of How to adjust Validations to achieve same result.

[jaredmccullough]

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

[jaredmccullough]

Bump....Any help would be appreciated

[Tinbendr]

I looked at your sheet yesterday, but I'm unwilling to guess at what you mean by

"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".

Modify your sample WB to what you want the final result to look like, then, I will take a look.

[jaredmccullough]

I looked at your sheet yesterday, but I'm unwilling to guess at what you mean by

Modify your sample WB to what you want the final result to look like, then, I will take a look.

Tinbendr,

What I have done was modified the original WB posted into the desired new format All WS have besides "Home" and "PSM-04-03A" have been combined into 1 WS and the title for the data from the original sheet names was added to columnD of "Recommendations" in the new file. (i.e. I did not add all files just some so you could see what I was getting at). So using the two WBs as an example the Isomeration Unit/MEK Dewaxing Unit/ROSE Unit tabs have been combined into one tab and a new columnD added to signify what used to be done with the "tab". Now if you look at the validation on WS "PSM-04-03A" on the first WB it utilizes the WS Names to populate a list in cell A4 and cell C4 then populates with all columnA items from that WS. Now that I have combined the data I still need the validations to populate the same. So A4 in the new WB would populate with all non repeats of the unit in ColumnD of the WS "Recommendations" and then cell C4 would populate with the filtered columnA results based upon the columnD choice.

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

[Tinbendr]

I'm not very skilled at noncontiguous validation lists, so for the sake of time, I wrote it using comboboxes.