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