How do I reference a file that does not exist yet

  • Thread starter Thread starter Shayan
  • Start date Start date
S

Shayan

Hello Everyone.
I am working on creating monthly summary sales figures in an exce
file, these figures are from data in excel files in another folder.
am trying to make the worksheets for the upcoming fiscal year (so th
files that I need to reference do not exist yet) but when i go t
reference the files which are not yet existing, i get "file not found"
is there any way to use a function to get around this error ????

Thank you for your hel
 
Shayan,

You need to use formulas that create formulas to create the formulas, and a
macro to convert the formula resulting from the formula to the formula that
actually makes the link. Confused? How about an example?

Let's say you want to reference a workbook that doesn't exsist yet (for
tomorrow, WKBK050604.XLS), using the formula

='C:\excel\[WKBK050604.XLS]Data Sheet'!A1

So use the formula

="='C:\excel\[WKBK"&TEXT(NOW(),"MMDDYY")&".XLS]Data Sheet'!A1"

Today, that will simply show (but it won't be an actual link to the
spreadsheet)

='C:\excel\[WKBK050504.XLS]Data Sheet'!A1

But if tomorrow you select that cell and run the macro:

Sub Convert()
Selection.Formula = Selection.Text
End Sub

It will convert to a valid link.

HTH,
Bernie
MS Excel MVP
 
Back
Top