# Amend Formula to extract year from Cell

#### howard

##### Well-known Member
I have the following formula below

Code:
`` =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

Code:
``=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

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Special-K99

##### Well-known Member
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

##### Well-known Member
Thanks for the help

#### howard

##### Well-known Member
I would like to know how amend formula so that B1 become C1, D1 etc when copied across

Comms by month'!B1"),

#### Special-K99

##### Well-known Member
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

##### Well-known Member
Thanks for the help. It works perfectly