Indirect Address of workbook name

[Jaamie]

I have the following Offset statement which works just fine. However, I would like to be able to indirectly address other similar workbooks. I'd like the Book name and Sheet name to be in fixed cell or cells that the Offset statement references. For example the Book/Sheet can be in a fixed cell say J69 which could also be a Named Cell. I would like to keep the C1 in the Offset statement as I want several similar offset statements referencing different cells. I have tried numerous options unsuccessfully. Can I do it and if so, how?

I appreciate any help.

=OFFSET('[Ally ..5725.xlsm]Ally ..5725'!C1,$I$69-1,0)

 

[jasonb75]

If you want C1 to adjust when you drag the formula down then you would need to use R1C1 references in the formula. There are other ways, but they make it more complicated than necssary.

In the formula below I have assumed workbook name and sheet name will always be the same (as per the example given) and that the name is entered into J1 once, e.g Ally ...5725
The additional characters needed in the names have been added as part of the formula.

=OFFSET(INDIRECT("'["&J1&".xlsm]"&J1&"'!RC[-1]",0),$I$69-1,0)

The R1C1 reference in bold will need to be adjusted for the location of the formula relative to C1. RC[-1] is assuming the formula is in D1. The numbers inside the square brackets work the same as offest row and column relative to the cell with the formula, e.g OFFSET(D1,0,-1) would give you C1. In theory you might expect it to be R[0]C[-1] but the reference is not needed when the offset is 0.

 

[Jaamie]

We are getting closer, thank you. I am getting 0 as the answer and should be getting a date or text. I copied right several cells and all return 0.

The offset statement is in Cell E74 of Test.xlsm Sheet "Control"

Cell I69 contains the number 272. L69 contains Ally ..5725. I think this part is correct. The cell I am trying to reach is R272C1. Then another statement to reach R272C2, etc.

What should the RC be to get R272C1

=OFFSET(INDIRECT("'["&$L$69&".xlsm]"&$L$69&"'!RC[-1]",0),$I$69-1,0)

Again, thank you for your help.

 

[jasonb75]

The offset statement is in Cell E74

Try this one to reference C1 as the offset starting point from E74.

=OFFSET(INDIRECT("'["&$L$69&".xlsm]"&$L$69&"'!R[-73]C[-2]",0),$I$69-1,0)

Although you could actually do it with just INDIRECT if you wanted to, I was trying to keep some similarity to your original formula so that you could understand it easier.

 

[Jaamie]

That does it. Thank you. Plus, I finally understand it. I wasn't sure what the RC[-1] was relative to. Thanks again.

 

[jasonb75]

Now that you understand how it works, the version without OFFSET. Probably not so easy to understand but hopefully of use.

=INDIRECT("'["&$L$69&".xlsm]"&$L$69&"'!R["&($I$69-ROW(E74))&"]C[-2]",0)

This time the formula is offsetting from the formula cell directly to the result rather than going back up to C1 first, as such the row offset is positive instead of negative.