Create a drop down list and value pulls all data from another sheet

I have a complex design that I am trying to achieve. I have exhausted all ideas, and search and spent many hours of research. Hoping you guys can help me out.


Referring to the attached this is what I am looking for:

I have a Sheet named "MAINAUDIT"

This "MAINAUDIT" will contain a drop down list with these values:

ALL-17-ITEMS
EXCLUDING -DESIGN -DEVELOPMENT
EXCLUDING- ESD
EXCLUDING - DESIGN -ESC

Each of these drop down list values pulls the list of data following this logic and pastes a copy of the list into MAINAUDIT sheet under the Drop Down list:
IF ALL-17-ITEMS then load the data from Sheet ALL-17-ITEMS
If EXCLUDING -DESIGN -DEVELOPMENT the load the data from sheet EXCLUDING -DESIGN -DEVELOPMENT
IfEXCLUDING- ESD the load the data from sheet EXCLUDING- ESD
If EXCLUDING - DESIGN -ESC then load the data from sheetEXCLUDING - DESIGN -ESC


{FYI Only} - Each of the ITEM* sheets populates all four sheets:
(ALL-17-ITEMS
EXCLUDING -DESIGN -DEVELOPMENT
EXCLUDING- ESD
EXCLUDING - DESIGN -ESC)
values in their respected G Columns


Another piece of logic I need incorporated is:

Depending on which of the Four sheets the end user selects, I need to have the total score column "MAXIMUM SCORE PER QUESTION", to be populated on the SUMMARY sheet in B8.

As well in sheet SUMMARY the value selected in the Drop Down list from MAINAUDIT needs to be populated in SUMMARY.B7

Hope this makes sense.

I truly appreciate any assistance in this!

2-SUPPLIER AUDIT REPORT - JD.xlsx

and pastes a copy of the list into MAINAUDIT sheet under the Drop Down list:

Where is this exactly? A10? It would be better to copy the range from the sheet selected (A1:H18) and add move the Summary and Conclusions under that to a different cell.

Why not have a hyperlink to the worksheet chosen instead of the complication of copying the worksheet? This way, edits will not have to be copied back if there are edits. On the Summary worksheet have Data Validation drop down lists to bring in the data required without the intermediate step of copying a worksheet.