Google Sheets: Using a cell value, to dynamically create a formula, that links to a cell in a different Sheet (Tab)

[blafarm]

Ok. This is a bit complicated , but I will do my best to describe the problem. All of these elements are located in multiple Sheet pages (Tabs) within one singular Google Sheet (Workbook).



Sheet 1: The MENU Sheet (Tab Name: Menu) allows users to enter a numerical value in the yellow highlighted cell (E6), and then hit the blue "Go" link to navigate to the DISPLAY Sheet:

Sheet 2: The DISPLAY Sheet (Tab Name: Display) allows the user to see the desired data based on their selection in the MENU Sheet.


There is no real data in this page -- it is a blank page in which all the cells contain formula references to other DATA Sheets (tabs) that are numbered 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ,11.
For example, if the user has selected Item #1 in the MENU Sheet, the cells in Column A in this page would contain formulas that look like this: =1!A2, =1!A3, =1!A4, =1!A5. Alternatively, if the user selected Item #2 in the MENU Sheet, the cells in Column A in this page would contain formulas that look like this: =2!A2, =2!A3, =2!A4, =1!A5.
The goal here is to use the value in the yellow highlighted cell (E6) in the MENU Sheet to dynamically construct the formulas in this page so that they display the correct DATA Sheet.

Sheet 3: The DATA Sheets (Tab Names: 1 through 11) need to be dynamically displayed in the DISPLAY Sheet and look like this :

PROBLEM:
This overall goal of this design is to have one singular DISPLAY Sheet that is able to dynamically display all the data from the individual DATA Sheets based on the user's MENU selection.

This requires that all of the formulas, in all of the cells of the DISPLAY Sheet have the ability to use the numerical selection in the MENU Sheet in order to point to the proper DATA page.

I have spent many unsuccessful hours trying to create a formula in the DISPLAY Sheet that accomplishes this task.

I started with this: =Menu!E6 & B2 -- in the hopes that it would create a formula that looks like this: =1!B2 that would display the contents of cell B2 in the DATA Sheet named: 1 However, it only populates the DISPLAY Sheet cell in which it resides with a number: 1 followed by the contents of cell B2 in the DISPLAY Sheet -- and not cell B2 in the DATA Sheet named 1

I have tried many, many other variations that are not worth copying here -- all with no success.

I would be very grateful for any assistance with this problem.

 

[blafarm]

I have made some good progress after finding: =INDIRECT

This formula works perfectly: =INDIRECT(Menu!$E$6 & "!B2")

However, I need to copy this formula down hundreds of rows -- and the "!B2" in the formula needs to increment on a row-by-row basis to "!B3, "!B4", "!B5", "!B6" ... all the way to "!B500"

And I need to do that for 5-10 columns.

I have tried a number of workarounds -- but none of them work.

Does anyone have any suggestions?

Thanks very much in advance.

 

[blafarm]

Ok. Looks like I overcame that problem.
I'll document it in case anyone else stops by here.
So I started with this formula: =INDIRECT(Menu!$E$6 & "!B2")
But I could not get the "!B2" to increment higher as I copied the formula down the column.
So I copied the "!B2" part of the formula to a different column (Column J) without the double quotation marks.
As there are not quotation marks, the !B2 increments as expected, and I then referenced that new column in the formula.
Now the working formula is: =INDIRECT(Menu!$E$6 & J2)