Concatenate cell value to the file name

[spamitovic]

Hi,

I have different excel files, each of them is related to a specific month, named 12020.xlsx, 22020.xlsx, 32020.xlsx,...

And I created another file name dashboard, will contains all data from different files.

In a specific cell, Il linked the first file like that:

=IF(A10=[92020.xlsx]Présence!A5;NB.SI([92020.xlsx]Présence!A5:AE5;"CP");0)

I create in a cell, a list of all months: 1, 2, 3,...

I need to concatenate the value of the selected value to the file name. So, if I select 1 (january), the formula will change to:

=IF(A10=[12020.xlsx]Présence!A5;NB.SI([12020.xlsx]Présence!A5:AE5;"CP");0)

I tried to use the concatenete formula but it doesn't work.

[#2]

Hi @spamitovic,

The following formula will return the current file name.

=RIGHT(CELL("filename"),LEN(CELL("filename"))- MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"), ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))

So I'm assuming that using something like this should do the trick:

=IF(A10=[RIGHT(CELL("filename"),LEN(CELL("filename"))- MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"), ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))]Présence!A5;NB.SI([92020.xlsx]Présence!A5:AE5;"CP");0)