Dynamic Reference

[Tyler.C.Brown]

Is their any way to return a value from a dynamic reference to a closed
spreadsheet?

 

[Shane Devenshire]

Yes.

But you're not giving us any info. What is your reference formula?

 

[Tyler.C.Brown]

I have a folder with identically formated files. The names are different by
the first two characters which represent the month in which its data
contains. From another document I am using the INDIRECT function to swith
the data to a specific month. I've got the link to work as long as the
spreadsheet I am linking to is opened after the INDIRECT value is changed.
This is what my formula looks like right now.

B4 is the file path and C4 is the cell location.
=INDIRECT("'" & B4 & "'!" & C4)

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

 

[Tyler.C.Brown]

Thanks for the info Dave.

Do you know if this addin would have to be added to all users of the doc. I
am working on? I currently set up a macro based of a list box that will
Dynamically open and close the spreadsheet in which the cell would be
referencing.

 

[Dave Peterson]

Yep. Every user that needs the workbook would need the addin. I haven't looked
at this in a longggggg time, but I thought that there was a way to extract just
that one function and embed it into the workbook.

I don't understand the second question. You'd copy the range that contains the
data and then paste to whereever you wanted (clearing the contents of the
previous paste).

 

[Tyler.C.Brown]

That answers it. I was just mentioning that to get the dynamic reference to
work I found that once it was dynamically changed (INDIRECT FUNTION).

example

=JAN 09VARIANCE.xlxs!A1
to
=FEB 09 VARIANCE.xlxs!A1

I would have to then open the corresponding spreadsheet. (JAN
09VARIANCE.xlxs!A1) once that happened it worked.

So I set up a list box allowing the user to pick the month they wanted to
look at Jan 09, feb 09 etc. Upon this selection being changed, I have a
macro that would then open and close the coresponding sheet, thus making the
reference valid. I'm not sure if it is the best work around but seems to be
ok.

Thanks for your help.

 

[Dave Peterson]

Another way is to open the monthly file and copy the range to a different
worksheet (hidden so it doesn't bother the user), then name that range nicely.
And close the sending workbook.

Then you don't have to worry about someone tying up that file.

dim ListWks as worksheet
dim MonthWkbk as workbook

set listwks = worksheets("HiddenList")
set monthwkbk = workbooks.open(filename:="C:\whatever.xlsx", readonly:=true)

with listwks
.cells.clear
monthwkbk.worksheets("somesheet").range("A:A").copy _
destination:=.range("A1")
.parent.Names.Add Name:="myList", _
RefersTo:="=OFFSET('" & .name & "'!$A$1,0,0,COUNTA('" _
& .name & "'!$a:$a),1)"
end with
monthwkbk.close savechanges:=false


(Untested, uncompiled.)

That answers it. I was just mentioning that to get the dynamic reference to
work I found that once it was dynamically changed (INDIRECT FUNTION).

example

=JAN 09VARIANCE.xlxs!A1
to
=FEB 09 VARIANCE.xlxs!A1

I would have to then open the corresponding spreadsheet. (JAN
09VARIANCE.xlxs!A1) once that happened it worked.

So I set up a list box allowing the user to pick the month they wanted to
look at Jan 09, feb 09 etc. Upon this selection being changed, I have a
macro that would then open and close the coresponding sheet, thus making the
reference valid. I'm not sure if it is the best work around but seems to be
ok.

Thanks for your help.