Use ampersand to change year in formula

[howard]

I have the following formula

 =INDEX('[Sales Report2019.xlsm]Sales by month'!$A$11:$N$11,MATCH('[Sales Report2019.xlsm]Sales by month'!B1,'[Sales Report2019.xlsm]Sales by month'!$A$1:$M$1))

I have the year in Q1


I would like to use an ampersand to combine the year in the formula with the year , but I cannot get it to work foe e.g Sales Report"&Q1&".xlsm


It would be appreciated if someone could kindly assist me

 

[Scott T]

When you do that it turns into text and is no longer a cell reference look at the indirect function.

Note that indirect does not like closed workbooks so Sales report2019 will need to be open when this workbook is opened

https://support.office.com/en-us/article/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261

 

[howard]

Thanks for the link