TEXT Function

%3CLINGO-SUB%20id%3D%22lingo-sub-3130390%22%20slang%3D%22en-US%22%3ETEXT%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3130390%22%20slang%3D%22en-US%22%3EI%20have%20a%20problem%20with%20some%20select.%3CBR%20%2F%3ECan%20you%20help%20me%20with%20some%20example%20about%20%22TEXT()%22%20FUNCTION%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20to%20select%20custom%20data%20from%20another%20sheet%2F%20or%20another%20excel%20file%20with%20text%20function%20but%20I%20don't%20find%20some%20example%20with%20this%20subject.%3CBR%20%2F%3E%3CBR%20%2F%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20standard%20formula%20is%20%3A%26nbsp%3B%3D'%5BSampleData.xlsx%5D06.02.2022'!%24E%242%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20to%20select%20custom%20sheet(%20my%20sheet%20name%20is%20data%2C%20I%20create%20a%20new%20sheet%20for%20every%20day).%3CBR%20%2F%3E%3CBR%20%2F%3ETomorrow%20the%20name%20of%20new%20sheet%20will%20be%2007.02.2022.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3130390%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3131398%22%20slang%3D%22en-US%22%3ERe%3A%20TEXT%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3131398%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1299809%22%20target%3D%22_blank%22%3E%40redgeckos%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20the%20sheet%20is%20in%20another%20workbook%20that%20is%20open%20in%20Excel%2C%20you%20can%20use%20the%20INDIRECT%20function%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDIRECT(%22'Sample%20Data.xlsx%5D%22%26amp%3BTEXT(TODAY()%2C%22dd.mm.yyyy%22)%26amp%3B%22'!E2%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3131749%22%20slang%3D%22en-US%22%3ERe%3A%20TEXT%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3131749%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1299809%22%20target%3D%22_blank%22%3E%40redgeckos%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlas%2C%20INDIRECT%20does%20not%20work%20with%20closed%20workbooks.%20There%20is%20no%20good%20solution%20for%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3131753%22%20slang%3D%22en-US%22%3ERe%3A%20TEXT%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3131753%22%20slang%3D%22en-US%22%3EI%20have%202%20workbook%2C%20one%20for%20data%20and%20second%20for%20formula%20and%20view.%3CBR%20%2F%3EWhat%20can%20I%20use%3F%3C%2FLINGO-BODY%3E
New Contributor
I have a problem with some select.
Can you help me with some example about "TEXT()" FUNCTION?

I want to select custom data from another sheet/ or another excel file with text function but I don't find some example with this subject.

 

My standard formula is : ='[SampleData.xlsx]06.02.2022'!$E$2

I want to select custom sheet( my sheet name is data, I create a new sheet for every day).

Tomorrow the name of new sheet will be 07.02.2022.
7 Replies

@redgeckos 

If the sheet is in another workbook that is open in Excel, you can use the INDIRECT function:

=INDIRECT("'Sample Data.xlsx]"&TEXT(TODAY(),"dd.mm.yyyy")&"'!E2")
But, how can i update the excel where i have formula without open the second excel ?
If i don't have the second excel open in cell where i have formula i give #ref

@redgeckos 

Alas, INDIRECT does not work with closed workbooks. There is no good solution for that.

I have 2 workbook, one for data and second for formula and view.
What can I use?

@redgeckos 

Either keep both open, or move the sheets from both into a single workbook.

Why not simply move the two workbook info one sheet to save yourself some undue areas or simply store your data in MS SQL and connect to Excel and do whatever you want
This excel need to be show on tv and second excel is for store same data, i modify the excel some times per day.
I try to put data in sql and afther that made a connection with excel.