Formula with absolute cell reference / cell reference increase

[MrHydrant1857]

Hi all,
I am having an issue with an idea I am trying to play around with at the moment. The overall idea to my workbook is to have a main sheet with a table and then multiple other sheets with the "same" table. The non main sheets will all have different data in them but the column headers will be the exact same. for example, sheet 2, named "Color Sheet 1", will have a column header of Color and the first row will say Red. then sheet 3, named "Color Sheet 2", will have a column header of Color and the first row will say Blue. I have a button on my main sheet that opens up a user form which has a dropdown list of all my sheet names. What I want to happen is when i select a certain sheet in the user form is to have that selection be put in a hidden cell, then use that to be the criteria for the table. So for example if i selected "Color Sheet 1" then the table will show the color Red. The issue i am running into is that my formula on my main table is not increasing one of the cell references. the formula i started with in row one is, =INDIRECT("'"&$Q1&"'!B2") . Q1 is the hidden cell reference that needs to be absolute. The cell reference "B2" i need to have increase to B3,B4,B5 an so on when i drag the formula down the column. the formula remains the exact same when i try to drag down.

So my question is,
Is there any way that I can get that "B2" to increase when dragging the formula down without having to do it manually? I am needing this to possibly go to a few hundred rows.

I appreciate any feedback! if any additional information is needed please let me know!

 

[6StringJazzer]

Try this:

=INDIRECT("'"&$Q1&"'!" & CELL("address",B2))

 

[MrHydrant1857]

Try this:

=INDIRECT("'"&$Q1&"'!" & CELL("address",B2))

This worked Great!!! the only addition i had to make to your formula is add the "$" before the "1" so that it kept the absolute row reference for Q1. i appreciate it!!

 

[6StringJazzer]

Yes, I didn't notice that since it was that way in your original formula. Feel free to mark my post as the solution.