Forum Discussion

Copper Contributor

Sep 16, 2020

Concatenate cell value to the file name

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 di...

excel

Formulas and Functions

Macros and VBA

office 365

Bennadeau

Iron Contributor

Sep 17, 2020

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)