Change Reference to Another Sheet on Cell A, based on value of Cell B

[Corsan]

Hi Team,

I'm wondering if you can assist.. i'm wondering if you can advise on the below. Let's say I have a cell that is basing it's result on a reference to another cell in another workbook.. such as this..

='L:\Documents\[Main Sheet.xlsm]KPI'!$B$5

How would I change it where the 'Main Sheet' part of this formula is based on another cell in the same sheet as the formula?

So for example... let's say the above formula is on A:1. Let's say whatever I change on B:2, defines to the exact wording on where 'Main Sheet' would be in the formula...

How would I go about doing this?

 

[Corsan]

Would INDIRECT work for this for example?

 

[Corsan]

Just following up everyone. Is this impossible to do on Excel?

 

[DanteAmor]

Try this:

=INDIRECTO("'L:\Documents\[" & B2 & ".xlsm]KPI'!$B$5")

Note: the indirect function to another book only works if the other book is open, if you close the book you can return an error #¡REF!