Concatenate cell value to the file name

%3CLINGO-SUB%20id%3D%22lingo-sub-1675912%22%20slang%3D%22en-US%22%3EConcatenate%20cell%20value%20to%20the%20file%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1675912%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20different%20excel%20files%2C%20each%20of%20them%20is%20related%20to%20a%20specific%20month%2C%20named%2012020.xlsx%2C%2022020.xlsx%2C%2032020.xlsx%2C...%3C%2FP%3E%3CP%3EAnd%20I%20created%20another%20file%20name%20dashboard%2C%20will%20contains%20all%20data%20from%20different%20files.%3C%2FP%3E%3CP%3EIn%20a%20specific%20cell%2C%20Il%20linked%20the%20first%20file%20like%20that%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(A10%3D%5B92020.xlsx%5DPr%C3%A9sence!A5%3BNB.SI(%5B92020.xlsx%5DPr%C3%A9sence!A5%3AAE5%3B%22CP%22)%3B0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20create%20in%20a%20cell%2C%20a%20list%20of%20all%20months%3A%201%2C%202%2C%203%2C...%3C%2FP%3E%3CP%3EI%20need%20to%20concatenate%20the%20value%20of%20the%20selected%20value%20to%20the%20file%20name.%20So%2C%20if%20I%20select%201%20(january)%2C%20the%20formula%20will%20change%20to%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(A10%3D%5B12020.xlsx%5DPr%C3%A9sence!A5%3BNB.SI(%5B12020.xlsx%5DPr%C3%A9sence!A5%3AAE5%3B%22CP%22)%3B0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20tried%20to%20use%20the%20concatenete%20formula%20but%20it%20doesn't%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1675912%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1678296%22%20slang%3D%22en-US%22%3ERe%3A%20Concatenate%20cell%20value%20to%20the%20file%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1678296%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F782832%22%20target%3D%22_blank%22%3E%40spamitovic%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EThe%20following%20formula%20will%20return%20the%20current%20file%20name.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DRIGHT(CELL(%22filename%22)%2CLEN(CELL(%22filename%22))-%20MAX(IF(NOT(ISERR(SEARCH(%22%5C%22%2CCELL(%22filename%22)%2C%20ROW(1%3A255))))%2CSEARCH(%22%5C%22%2CCELL(%22filename%22)%2CROW(1%3A255)))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ESo%20I'm%20assuming%20that%20using%20something%20like%20this%20should%20do%20the%20trick%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(A10%3D%5BRIGHT(CELL(%22filename%22)%2CLEN(CELL(%22filename%22))-%20MAX(IF(NOT(ISERR(SEARCH(%22%5C%22%2CCELL(%22filename%22)%2C%20ROW(1%3A255))))%2CSEARCH(%22%5C%22%2CCELL(%22filename%22)%2CROW(1%3A255)))))%5DPr%C3%A9sence!A5%3BNB.SI(%5B92020.xlsx%5DPr%C3%A9sence!A5%3AAE5%3B%22CP%22)%3B0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

1 Reply

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)