Calling out a cell in a formula based on value of another cell

[BKGLTS]

Hello All,

Here is example of cells on Sheet2:

A1 = 21

A2 = Sheet1!B21 (where 21 is pulled from A1 on Sheet2)


I tried A2 = Sheet1!B($A$1), but this didn't work.


Thank you!
B

 

[dreid1011]

Use the INDIRECT function, like so:

=INDIRECT("Sheet1!B"&$A$1)

 

[BKGLTS]

dreid1011,

This appears to work, but it won't copy/fill the formula horizontally as needed.
I modified your solution to the following:

=INDIRECT("Schedule!V"&$B$1-2)

My problem now is that when I copy/drag/fill to the right it copies the formula as is, but I need the V to change to W, then X, then Y, etc...

Is there a solution for this?

Thank you!
B

 

[dreid1011]

Yes, try this:

=INDIRECT("Schedule!"&SUBSTITUTE(ADDRESS(1,COLUMN()+21,4),"1","")&$B$1-2)

 

[BKGLTS]

Thank you this works great!

 

[dreid1011]

You're welcome, and thanks for the feedback