Referencing other tabs with a drop down

[lost_in_the_sauce]

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.

 

[kweaver]

Try this in the Compare sheet in B5:

=INDIRECT(A1&"!B5")

 

[lost_in_the_sauce]

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]

Try this for the 5th row dragged left or right.

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

getting ugly. LOL.

 

[lost_in_the_sauce]

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]

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.

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