Amend Formula to extract year from Cell

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,449
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
 

Some videos you may like

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
Joined
Nov 7, 2006
Messages
8,341
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
Joined
Jun 26, 2006
Messages
5,449
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
Joined
Nov 7, 2006
Messages
8,341
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")))
 

Watch MrExcel Video

Forum statistics

Threads
1,090,355
Messages
5,413,976
Members
403,511
Latest member
Emmanuel John

This Week's Hot Topics

Top