Using a variable in an external reference

[pibe31]

Hello everybody, I'm new to the forum but not new to Excel.

I'd appreciate some help regarding the INDIRECT function. Basically what I am trying to do is the following:

I have 2 workbooks located in the same directory named 'Club Members' and 'Tickets' which I want to link without making use of any fixed values. The corresponding worksheets are named 'Membership List' and 'Sheet1' respectively. 'Sheet1' gets data from 'Membership List'.

The working is done in 'Sheet1' and I am using cell K350 purely as an example.
='[Club members.xlsm]Membership List'!$K$350

What I need to do is substitute cell K350 with a variable and after reading other examples in some other inactive threads I understand that this is achieved with the INDIRECT function. I have achieved this so far however it seems I can't figure out the appropriate syntax for the INDIRECT function.

Inside 'Sheet1' -
In cell A1 I put the path as a string ----> '[Club members.xlsm]Membership List'!
In cell A2 I put the variable ----> 200
In cell A3 I put the INDIRECT function ---> =INDIRECT("'" & $A$1 & "'!A2")

I'm really not sure about the syntax of cell A3 though.

I want to use the variable in cell A2 so Excel considers it as ='[Club members.xlsm]Membership List'!$K$200.
I am getting a ZERO though. The source workbook 'Club Members' was open all the time.

Any help pointing me in the right direction is much appreciated thanks guys

[FDibbins]

Hi, welcome to the forum

1st thing to note is that INDIRECT does not work on closed workbooks

2nd, INDIRECT is used to replace text in a formula, so that exxel can use it in that formula. So, in your case, maybe something like...
A1='[Club members.xlsm]Membership List'!
A2=200
A3=INDIRECT("'" & $A$1 &"K"&A2)
You did not give a column. so I added it to be K

[pibe31]

Thanks for the warm welcome and for the response

I have done some extensive research about the INDIRECT function in the past hours but I still could not apply it to my example.

Yes, both workbooks as explained in my original post are open.
You were right about me failing to include a column. After applying your revised function now I am getting a #REF!
but when I try ='[Club members.xlsm]Membership List'!K200 I get the appropriate result though which means that the sheets are actually linked.

Somehow, either the syntax is still not correct or the variable is not being read properly.

[FDibbins]

Sorry for the delay

The INDIRECT is supposed to replace parts of the formula. So what I sometimes do, is create the formula in the normal way, using the mouse/keys - and make sure that is working. then I start to replace (whole) parts with INDIRECT. So if your formula is...
='[Club members.xlsm]Membership List'!$K$350

A1=Club members.xlsm
so the formula would be...
=INDIRECT("'["&A1&"]Membership List'!K350")
if that works OK, we move on and change more...
A2=Membership List
=INDIRECT("'["&A1&"]"&A2&"'!K350")
Then...
A3=350
=INDIRECT("'["&A1&"]"&A2&"'!K"&A3)
Test at each stage and make sure that part works - adjust and fiddle as needed

[pibe31]

You're the man, that worked perfectly. The INDIRECT function at step 3 is finally allowing me to use variables.

I fiddled one last time with the string and now I got =INDIRECT("'[Club members.xlsm]Membership List'!K"&A3).

Works to perfection. I will be reading more about this powerful function. Thanks once again.