Referencing other tabs with a drop down

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
I have a worksheet that I need to fill cells based on a drop down list (Data validation).

Luckily I don't have any formulas, just cell references.

For example, my tabs are labels 100, 200, 300. In tab "Compare", I want cell B5 to directly reference cell B5 on either tab 100, 200, or 300 based on a drop down lost in cell A1. I think I might be able to use INDIRECT but I've never used it so I'm watching YouTube videos now. I don't know any VBA so hopefully there is a normal solution.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,105
Office Version
  1. 365
  2. 2010
Try this in the Compare sheet in B5:

Code:
=INDIRECT(A1&"!B5")
 
Solution

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
That absolutely works. I think I understand the necessity of the & now.

Follow up question if you don't mind - That got me the direct reference to cell B5, but if I copy paste formula, Ctrl+R, or drag the box to copy over, every cell is still B5 - how do I get it to adjust with the cells?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,105
Office Version
  1. 365
  2. 2010
Try this for the 5th row dragged left or right.

Code:
=INDIRECT($A$1&"!"&ADDRESS(5,COLUMN()))

getting ugly. LOL.
 

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
It is. I need to also go vertical and given that the actual cells I'm mapping for start points are H6 looking at AT6, I'm wondering if I bit off more than I can chew
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,105
Office Version
  1. 365
  2. 2010
Now I'm a bit confused. Are the 100, 200, etc. sheets starting at B5 or somewhere else now?

If those sheets are starting with H6, but the Compare is in AT6, then this should work in AT6 dragging left, right, up, down.

Code:
=INDIRECT($A$1&"!"&ADDRESS(ROW(),COLUMN()-38))
 

Forum statistics

Threads
1,141,069
Messages
5,704,111
Members
421,327
Latest member
Msh

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