Using cell reference in formula

[jas1980]

I am tying to use a cell reference in 2 formulas.

The first is a hyperlink:
=HYPERLINK("C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\&$G$2&\MQL4\Files\BSA_ScreenShots\&$D24&_&$K24&_&$M24&.PNG","ClosePic")
I cannot get this to work, but when I first tried it worked and I cannot see what is different.
This is the same link (that works) manually added and without the cell references:
C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\C142B020C05FAD9EEC4BE1375F709241\MQL4\Files\BSA_ScreenShots\75496008_2021.02.14_20.43.47.PNG

This is the second that I have added cell references to:
TRIM(MID(SUBSTITUTE(,";",REPT(" ",LEN('C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\&$G$2\MQL4\Files\BSA_Transactions\[BSA_Transactions.csv]BSA_Transactions'!$A1))),(1-1)*LEN('C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\&$G$2\MQL4\Files\BSA_Transactions\[BSA_Transactions.csv]BSA_Transactions'!$A1)+1,LEN('C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\&$G$2\MQL4\Files\BSA_Transactions\[BSA_Transactions.csv]BSA_Transactions'!$A1)))

This is the formula that works without cell references:
=TRIM(MID(SUBSTITUTE('C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\C142B020C05FAD9EEC4BE1375F709241\MQL4\Files\BSA_Transactions\[BSA_Transactions.csv]BSA_Transactions'!$A2,";",REPT(" ",LEN('C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\C142B020C05FAD9EEC4BE1375F709241\MQL4\Files\BSA_Transactions\[BSA_Transactions.csv]BSA_Transactions'!$A2))),(1-1)*LEN('C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\C142B020C05FAD9EEC4BE1375F709241\MQL4\Files\BSA_Transactions\[BSA_Transactions.csv]BSA_Transactions'!$A2)+1,LEN('C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\C142B020C05FAD9EEC4BE1375F709241\MQL4\Files\BSA_Transactions\[BSA_Transactions.csv]BSA_Transactions'!$A2)))

Hopefully someone can see where I'm going wrong with these?

 

[steve the fish]

You have missing quotes. The cell references need to be outside the string parts so excel knows you mean the cell and are not being literal. eg this part:

"C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\&$G$2&\MQL4\Files\BSA_ScreenShots\&$D24&_&$K24&_&$M24&.PNG"

should be

"C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\"&$G$2&"\MQL4\Files\BSA_ScreenShots\"&$D24&"_"&$K24&"_"&$M24&".PNG"

 

[jas1980]

You have missing quotes. The cell references need to be outside the string parts so excel knows you mean the cell and are not being literal. eg this part:

"C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\&$G$2&\MQL4\Files\BSA_ScreenShots\&$D24&_&$K24&_&$M24&.PNG"

should be

"C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\"&$G$2&"\MQL4\Files\BSA_ScreenShots\"&$D24&"_"&$K24&"_"&$M24&".PNG"

Thank you for this, I will add to the sheet now

 

[jas1980]

The hyperlink formulas wok properly, but the trim still doesn't, I get a REF error when I add the cell reference to the formula like this:

=TRIM(MID(SUBSTITUTE('C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\"&$G$2&"\MQL4\Files\BSA_Transactions\[BSA_Transactions.csv]BSA_Transactions'!$A2,";",REPT(" ",LEN('C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\"&$G$2&"\MQL4\Files\BSA_Transactions\[BSA_Transactions.csv]BSA_Transactions'!$A2))),(1-1)*LEN('C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\"&$G$2&"\MQL4\Files\BSA_Transactions\[BSA_Transactions.csv]BSA_Transactions'!$A2)+1,LEN('C:\Users\jamie\AppData\Roaming\MetaQuotes\Terminal\"&$G$2&"\MQL4\Files\BSA_Transactions\[BSA_Transactions.csv]BSA_Transactions'!$A2)))

Is there a fix to stop this error?