Reference a tab name within a formula

[eak2006]

Below is an example of tab names that reference another file. Is there a way to create a formula in row 3 that references the other file VR tab and cell
R3 and then copy that formula over to column C and beyond (and row 4 would reference R4 in the other file). I have many columns. I understand that the indirect formula requires you to have the other file open which I cannot always do because the links in the other file would constantly change which I don't want to happen.

ColB ColC ColD ColE
Row 2 VR VC TB BH
Row 3
Row 4

Any help is greatly appreciated!

 

[aaronbens]

I assume VR is in column B, if so and the tabs are in the same workbook then this should work: =INDIRECT(B$2&"!R"&ROW())

 

[eak2006]

Thank you very much, you are correct with your assumptions. However I am referencing another file that I cannot always open. The referenced would get updated if I kept it open, it has to stay stagnant. So I can't use the indirect formula.