Amend Formula to extract year from Cell

[howard]

I have the following formula below

 =INDEX('C:\My Documents\[Comms2019.xlsm]Comms by month'!$A$11:$N$11,MATCH('C:\My Documents\[Comms2019.xlsm]Comms by month'!B1,'C:\My Documents\[Comms2019.xlsm]Comms by month'!$A$1:$M$1))

I would like to amend the formula to extract the year from cell P1 so when the year in P1 changes the formula containing the year chnges automatically



I have the year in cell P1 and have tried to amend formula

=INDEX('C:\My Documents\[Comms2019.xlsm] to extract this from P1 eg  =INDEX('C:\My Documents\[Comms"&P1&".xlsm]

It would be appreciated if someone could assist me

 

[Special-K99]

Try (untested)

=INDEX(INDIRECT("'C:\My Documents\[Comms" & P1 & ".xlsm]Comms by month'!$A$11:$N$11"),MATCH(INDIRECT("'C:\My Documents\[Comms" & P1 & ".xlsm]Comms by month'!B1"),INDIRECT("'C:\My Documents\[Comms"& P1 & ".xlsm]Comms by month'!$A$1:$M$1")))

 

[howard]

Thanks for the help

 

[howard]

I would like to know how amend formula so that B1 become C1, D1 etc when copied across

Comms by month'!B1"),

 

[Special-K99]

Starting to get complicated...!
Untested again

=INDEX(INDIRECT("'C:\My Documents\[Comms" & P1 & ".xlsm]Comms by month'!$A$11:$N$11"),MATCH(INDEX(INDIRECT("'C:\My Documents\[Comms" & P1 & ".xlsm]Comms by month'!B1:ZZ1",1,COLUMNS($A1:A1)),INDIRECT("'C:\My Documents\[Comms"& P1 & ".xlsm]Comms by month'!$A$1:$M$1")))

 

[howard]

Thanks for the help. It works perfectly