indirect reference to refer to cell

[howard]

I have the following formula below


=LOOKUP(2,1/((B$1='C:\My Documents\[Sales.2020.2021.xls]Mar-2021'!$B$7:$B$40)*($F$1=EOMONTH('C:\My Documents\[Sales.2020.2021.xls]Mar-2021'!$M$1,0))),'C:\My Documents\[Sales.2020.2021.xls]Mar-2021'!$H$7:$H$40)/0.15

I have the month and year on sheet "summary' in cell D1

i would like to make use of the indirect formula , so that when the month and year in D1 changes to say Apr-2021 , the sheet on the workbook will charge to refer to the month and the year

for e.g. id D1 is changed to Apr-2021 the workbook in the lookup formula to chnge to

 'C:\My Documents\[Sales.2020.2021.xls]Apr-2021'!$B$7:$B$40

your assistance is most appreciated

 

[Fluff]

You're welcome & thanks for the feedback.