Hello,
I have work book that I use to combine different options from established roles to create a new role. in the attached book TESTROLES.xlsx I have a small sample of what I have set up (the actual workbook is huge). Here is what I am doing.

Sheet1 is the original or base roles and options. the roles are the top row and the options are in column A. Row 2 is just the number of the column for use in a formula. These sheets do not change.
The OUTPUT sheet has the same column headers and column A as Sheet1. Row 2 contains this formula to match up to the INPUT sheet: =IF(AND(B1=INDIRECT(ADDRESS(COLUMN(),1,,,"NEW ROLE")),INDIRECT(ADDRESS(COLUMN(),2,,,"NEW ROLE"))="Y"),"Y","")
The rest of OUTPUT pulls in the corresponding column from sheet1 using this formula: =IF(B$2="Y",VLOOKUP($A3,Sheet1!$A$1:$E$7,Sheet1!B$2,FALSE),"")
I then added a column (called output) that combines the results of the rest of the columns using this formula =IF(COUNTIF(B3:E3,"F")>0,"F",IF(COUNTIF(B3:E3,"AU")>0,"AU",IF(COUNTIF(B3:E3,"V")>0,"V",IF(COUNTIF(B3:E3,"N")>0,"N",""))))
in the INPUT sheet you would create a new role by marking Y/N to the desired roles that you'd like to combine (column A).
in the NEW ROLE sheet you would then choose your new role name from the drop down in cell B1. This will populate the results of your Y/N selections on the INPUT sheet in cells B2:B5 (this is really just to double check that the correct roles are being selected)
In the NEWROLE sheet cells E2:E6 are populated with the output column in the OUTPUT sheet

Now here is my delema.
My master list has four sheets so I had to create OUTPUT1, OUTPUT2, OUTPUT3, etc... for each base sheet. These sheets are extremely large (one is 16,000 rows) so anytime I make a change it takes a while to compute the results. It also makes the workbook a pretty large file so I can't always email it.

I would like to have a way to only have the base sheets, the INPUT sheet and the NEW ROLE sheet without having to have the different output sheets.
In other words is there a way to tell the workbook to take the selected roles and combine them onto a new page without having to have all of the OUTPUT pages.

I know this sounds confusing but hopefully the attched file will help make sense of it all.\
Thanks in advance
Peter