My foruma won't work but I can't see why

Hello,

I'm creating a very simple formula which takes cell Q11 from cell O11 and divides by 12: =(O11-Q11)/12
Next, this formula is performed in a different sheet to the cells. So, it becomes: =('Sheet2'!O11-'Sheet2'!Q11)/12
Finally, this formula is part of a template file which can be pasted into other files, so the document name becomes part of the formula: =('[Document.xlsx]Sheet2'!O11-'[Document.xlsx]Sheet2'!Q11)/12

To allow the formula to point to the document it has been pasted into, I have the current file name in cell B2 and I use an INDIRECT(CONCATENATE to point to cell B2, and then add the formula

However, although my syntax looks like it is generating the exact right text, I am getting a #VALUE! error

Below is what I currently have, I am hoping there is something obvious and silly I have overlooked like a parenthesis...

=INDIRECT(CONCATENATE("=('[",$B2,"]Sheet2'!O11")-CONCATENATE("'[",$B2,"]Sheet2'!Q11)/12"))

Try it like

Formula:

Please Login or Register  to view this content.

@Fluff13 you jolly well did it! Well done. I followed your steps and can see where I went wrong, I guess I should have included a second INDIRECT for a start, and the CONCAT was making it all rather messy.

Thanks again :-)

Glad to help, but after seeing John's post, realised that I have missed some brackets & it should be

Formula:

Please Login or Register  to view this content.

otherwise it dividing Q11 by 12 1st.