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

blafarm

Board Regular
Joined
Oct 14, 2018
Messages
59
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:


Menu.png

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.

Display.png



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

Data.png





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.
 
Last edited:

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

blafarm

Board Regular
Joined
Oct 14, 2018
Messages
59
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

Board Regular
Joined
Oct 14, 2018
Messages
59
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)




 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top