Use text string as formula

Hi,

I have this formula in my worksheet: ='C:\Fordelingsark\[Fordelingsark 2014-15.xlsx]4.2.16'!$AC$90

I want the date (marked in red) to be changed to todays date.

In other words, the formula will change from day to day.

The formula is in cell B3.
The date is in cell B1.

It will be something like this: ='C:\Fordelingsark\[Fordelingsark 2014-15.xlsx]&B1&'!$AC$90

but it won't work.


Any ideas?

Hi sandy666, thanks for quick reply.

This seems to work if I enter the date as text: '4.2.16

But when use =TODAY() and format cell as Date, it will show the correct date and format in B1, but I think the formula in B3 pull it out as a number (42404) and the result is #REF!

then use second version of formula
tab name is a text not a date format

I tried =TEXT(TODAY(),"d.m.yy")
but it returns "4.2.yy", not 4.2.16

Aha that worked. However, I want it to work without C:\Fordelingsark\[Fordelingsark 2014-15.xlsx is open.

If I use the original formula, the value in B3 will be updated when i update the links in the workbook, even when the workbookbook with the linked cell isn't open.

Is that possible?

INDIRECT() function doesn't work with closed file.

Ok, thank you for your help!

You are welcome

If problem from post #1 is resolved mark thread as solved. Thank you

As Sandy has said, INDIRECT does not work with closed files. If it is important to you to not have the "sending" file open, then you might like to search for and download the free add-in morefunc, which has a new function INDIRECT.EXT, and this does work with closed files.

Hope this helps.

Pete