Okay. This can be a bit difficult, however I’ll do my greatest to explain the issue. All of those components are positioned in a number of Sheet pages (Tabs) inside one singular Google Sheet (Workbook).
Sheet 1: The MENU Sheet (Tab Title: Menu) permits customers to enter a numerical worth within the yellow highlighted the cell (E6), after which hit the blue “Go” hyperlink to navigate to the DISPLAY Sheet:
Sheet 2: The DISPLAY Sheet (Tab Title: Show) permits the person to see the specified knowledge primarily based on their choice within the MENU Sheet.
There is no such thing as a actual knowledge on this web page — it’s a clean web page wherein all of the cells include components references to different DATA Sheets (tabs) which are numbered 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11.
For instance, if the person has chosen Merchandise #1 within the MENU Sheet, the cells in Column A on this web page would include formulation that appear like this: =1!A2, =1!A3, =1!A4, =1!A5. Alternatively, if the person chosen Merchandise #2 within the MENU Sheet, the cells in Column A on this web page would include formulation that appear like this: =2!A2, =2!A3, =2!A4, =1!A5.
The purpose right here is to make use of the worth within the yellow highlighted cell (E6) within the MENU Sheet to dynamically assemble the formulation on this web page in order that they show the right DATA Sheet.
Sheet 3: The DATA Sheets (Tab Names: 1 by way of 11) have to be dynamically displayed within the DISPLAY Sheet and appear like this:
This general purpose of this design is to have one singular DISPLAY Sheet that is ready to dynamically show all the information from the person DATA Sheets primarily based on the person’s MENU choice.
This requires that the entire formulation, in the entire cells of the DISPLAY Sheet have the flexibility to make use of the numerical choice within the MENU Sheet with a purpose to level to the correct DATA web page.
I’ve spent many unsuccessful hours attempting to create a components within the DISPLAY Sheet that accomplishes this activity.
I began with this: =Menu!E6 & B2 — within the hopes that it could create a components that appears like this: =1!B2 that may show the contents of cell B2 within the DATA Sheet named: 1 Nevertheless, it solely populates the DISPLAY Sheet cell wherein it resides with a quantity: 1 adopted by the contents of cell B2 within the DISPLAY Sheet — and never cell B2 within the DATA Sheet named 1
I’ve tried many, many different variations that aren’t value copying right here — all with no success.
I might be very grateful for any help with this downside.