Error in finding tab name formula

[ratedr95]

Hi there,

I am trying to write a formula (could potentially be easier in VBA but haven't tried that route) which will look at a document called PL and compare the value of a cell on a particular sheet within PL to the another cell on a document called P&L.

=[PL.xlsx]RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))!$C$8

This is what I am using for the formula at the moment, its not finished as it doesn't compare the values just yet mainly because it is not getting the value from the PL sheet.

I have tried writing the document location like

='P:\Desktop\BUDWIP\[PL.xlsx]

But it doesn't work and gives me an invalid references error highlighting the P: in that code.

Any suggestions on how I can make this work?

 

[Jonmo1]

Welcome to the board.

You need indirect for that.

Try
=INDIRECT("'[PL.xlsx]"&RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))&"'!C8")

 

[ratedr95]

If I could kiss you right now I would (not in a weird way)

I spent over two hours trying to get this to work and Googling etc and it was as simple as that!

THANK YOU!!

 

[Jonmo1]

You're welcome.