Referencing/mirroring the sheet through the drop down menu

Hello Everyone,

My name is Russ and I am new to this forum. Would like to thank you for all your posts and help.

I encountered a problem, could not find the answer here.

I have a master file with 30+ tabs. Each tab has its own product and online usage categories. I want to hide them all and create one sheet with selection menu from drop down. I want to choose the product from the menu and then basically see the all the info (mirror the info) to the current sheet from chosen tab.

My logic goes like this:
Step 1: Select the product from the drop down in Cell A1 ( ex. product "Apples")
Step 2: The current sheet populate the cells A2:Z500 from the tab "Apples". So now A2=Apples!A1
A3=Apples!A2 and be able to drag it along the sheet for all columns and rows.
Step 3. Someone else chooses product "Oranges" and sheet mirrors the content from tab Oranges.

Please let me know if you have questions. And thank you in advance!

Cheers