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]

Is D1 text or a proper date formatted to show mmm-yyyy?

 

[howard]

Hi Fluff

D1 is proper date formatted to show mmm-yyyy

 

[Fluff]

Ok, try it like

LOOKUP(2,1/((B$1=INDIRECT("'C:\My Documents\[Sales.2020.2021.xls]"&TEXT(D1,"mmm-yyyy")&"'!$B$7:$B$40"))

 

[howard]

Thanks Fluff

I tried to complete the rest of the formula using Indirect but get #n/A

 =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

If you need sample data, I will post this on Drop box tomorrow

 

[Fluff]

It would help if you posted your modified formula with the indirect.

 

[howard]

Will do so tomorrow

 

[howard]

Hi Fluff


See my links below containing the source and destination files

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Your assistance is most appreciated -see Formula B5 Sales indirect

 

[Fluff]

You have some of the brackets in the wrong place try

=LOOKUP(2,1/((D$1=INDIRECT("'C:\My Documents\[sales.2020.2021.xls]"&TEXT($F$1,"mmm-yyyy")&"'!$B$7:$B$40"))*($F$1=EOMONTH(INDIRECT("'C:\My Documents\[sales.2020.2021.xls]"&TEXT($F$1,"mmm-yyyy")&"'!$M$1"),0))),INDIRECT("'C:\My Documents\[sales.2020.2021.xls]"&TEXT($F$1,"mmm-yyyy")&"'!$H$7:$H$40")/0.15)

 

[howard]

Hi Fluff

Many thanks for the help. It works perfectly. Will watch one or two YouTube Videos on Indirect function to get a better understanding