Dinamic Sheet refrence outside the working Workbook

[Googamanga]

two workbooks with identical sheet names

Workbooks: Source, Target
Sheets eg: May, June, ...

[Target]June has a formula something like
index(F:\documents\[Source]June!$A$1:$D$4, match(target_id,source_id,0))

When July comes around, i would like to copy the June sheet in Target and create July. the fun part is that i would like all the formulas to automaticaly point at July in the Source workbook. (given that the July sheet is already created in the Source book)

i know how to work with sheet names using CELL("filename")

i was planning on using INDIRECT() but apperantly it doesn't like to work with closed source files

i heard about INDIRECT.ex addon (or something close to that name) that lets you work with closed source files, but the target workbook will be accessed and maintained by other users with no such addon....

So... (finaly the question) is there a way to update a refrences to a closed source file based on the target sheet name?

(did i confuse everyone???)

 

[schielrn]

Welcome to the Board!

Unfortunately without using the indirect() function you would need to use the indirect.ext() from the morefunc add-in, but as you said not everyone will have that add-in, so you could 1) add it to everyone's computers or 2) Use a macro to update the links. If you go the macro route, I would start by recording a macro that mimics you changing the link and modifying the code from there. Because if you put a macro in the workbook it would be live for all people that open that workbook.

Hope that helps.