Amend Formula to extract year from Cell

howard

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

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,314
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,374
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,314
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")))
 

Forum statistics

Threads
1,078,393
Messages
5,339,926
Members
399,340
Latest member
JasonT903

Some videos you may like

This Week's Hot Topics

Top