In agreement. This is a little complicated, but I will do my best to describe the problem. All these elements are found in several pages of Sheets (Tabs) within a unique Google Sheet (Workbook).
Sheet 1: The MENU sheet (Tab Name: Menu) allows users to enter a numeric value in the cell highlighted in yellow (E6), and then press the blue "Go" link to navigate to the SHOW Sheet:
Sheet 2: The SCREEN sheet (Name of the tab: Screen) allows the user to see the desired data according to their selection in the MENU Sheet.
There is no real data on this page; is a blank page in which all 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 Element # 1 on the MENU Sheet, the cells in Column A on this page would contain formulas like this: = 1! A2, = 1! A3, = 1! A4, = 1! TO 5. Alternatively, if the user selected Item # 2 on the MENU Sheet, the cells in Column A on this page would contain formulas like this: = 2! A2, = 2! A3, = 2! A4, = 1! TO 5.
The goal here is to use the value in the cell highlighted in yellow (E6) on the MENU sheet to dynamically build the formulas on this page so that they display the correct DATA sheet.
Sheet 3: The DATA sheets (Tab names: 1 to 11) should be displayed dynamically on the SCREEN Sheet and look like this:
The general objective of this design is to have a unique SCREEN sheet that can dynamically display all the data of the individual DATA sheets according to the selection of the user's MENU.
This requires that all formulas, in all cells of the DISPLAY sheet, have the ability to use the numeric selection in the MENU sheet to point to the appropriate DATA page.
I have spent many hours without success trying to create a formula on the SCREEN sheet that accomplishes this task.
I started with this: = Menu! E6 and B2 – with the hope that you create a formula that looks like this: = 1! B2 that shows the contents of cell B2 in the DATA sheet called: 1 However, it only fills the cell of the DISPLAY sheet 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 called 1
I have tried many, many other variations that are not worth copying here, all without success.
I would be very grateful for any help with this problem.