Token replace between two files with dynamic refresh

[MrSamExcel]

Text Result v1.xlsx contains text strings with [tokens] that need to be replaced with values from a separate file called Stats Token v2.xlsx. I want the tokens to update with values from the latest modified version of Stats Token that resides in the same folder as Text Result on my computer. For example, today Text Result v1 and Stats Token v2 are saved to my Desktop. Tomorrow, I change a STAT_VALUE in Stats Token v2 from 99 to 98 and save to my Desktop as Stats Token v3. Now when I open Text Result v1, the value in B2 should display “I got 98 problems” instead of 99. How can I set this up? Appreciate any suggestions.

The file names will be static before the version number "Text Result" and "Stats Token", but the version number needs to change every time there’s an update, such as v1 to v2. I have some flexibility with Stats Token file, could add helper columns for example. Users can’t use VBA.

Text Result v1.xlsx
	A	B	C
1	Text ID	Text	Result that should display in "Text" field after auto-pull variable from 'Stats Token*.xlsx'
2	paragraph1	I got [STAT ID 1 DISPLAY] and this is one.	I got 99 problems and this is one.
3	paragraph2	This month I'll eat [STAT ID 2 STAT_VALUE] [STAT ID 2 STAT_LABEL].	This month I'll eat 100 apple pies.
Text

Stats Token v1.xlsx
	A	B	C	D	E	F
1	FILE_ID	STAT_ID	AS_OF_DATE	STAT_VALUE	STAT_LABEL	DISPLAY
2	FILE1	STAT ID 1	6/30/2024	99	problems	99 problems
3	FILE1	STAT ID 2	9/30/2024	100	apple pies
4	FILE1	STAT ID 3	9/30/2024	500	miles
Stats

 

[Rijnsent]

Hi Sam,
for that you'd need an INDIRECT formula to build up the reference in your formula. See e.g. this explainer: Dynamic workbook reference So you'd need some place in your Text Result workbook where you define the name of your input file (so a cell with e.g. "file1.xlsx" or "file2.xlsx") and use that to build up your formulas.
Hope that works,
Koen