Variable Cell Reference

[neil1502]

Hello all,
I am not to sharp at this but here goes..

I have a large excel sheet that as a large number of references to other sheets the references mainly look like...

=\Site\General\CSD Self Reliability\2008-2009\Week 10\[Filler11_Wk10.xls]Wkly Downtime Sum'!T11

So this is referencing some information in Week 10 of the year.

Is there any easy way of putting in a variable to the filepath so that I can create a template and so by referencing a cell eg. i enter 30 in D2 the reference becomes


='\\Brnwcfnp01\Norwich Shared Area\Site\General\CSD Self Reliability\2008-2009\Week 30\[Filler11_Wk10.xls]Wkly Downtime Sum'!T11

Any clues or giudes would be gratefully recieved

Many Thanks
Neil

 

[Derek Brown]

Hello and welcome to The Board.
I have not tested my proposed solution but I would try using the INDIRECT function.
So the formula would look something like:
=INDIRECT("'\\Brnwcfnp01\Norwich Shared Area\Site\General\CSD Self Reliability\2008-2009\Week "&D2&"\[Filler11_Wk10.xls]Wkly Downtime Sum'!T11")
Note that INDIRECT will not work with a closed workbook - if you need to do that, have a look at using PULL which is code that you would need to add to your workbook. For this, search Google for "Harlan Grove PULL". Podcast 820 (http://www.mrexcel.com/podcast/labels/UDF.html) will probably show you how.

 

[DonkeyOte]

Re: closed targets - also see Laurent Longre's morefunc.xll add-in and specifically INDIRECT.EXT

Is it not viable to run an edit -> replace as and when required to update the Week references in the links ? INDIRECT is Volatile so normally best avoided if the intention is (as implied) this is to be used a number of times.

 

[neil1502]

Hello gents,
Thanks for the replies. I do indeed need it to take information from closed workbooks. I have got in indirect method to work on open workbooks as a trial.

I have started to try the halan grove pull code (see below)

Function pull(xref As String) As Variant

Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long

'** begin 2004-05-28 changes **
'** begin 2004-03-25 changes **
n = InStrRev(Len(xref), xref, "\")

If n > 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n > 0 Then b = b & Mid(xref, Len(b) + 2, n)

Else
n = InStrRev(Len(xref), xref, "!")
If n > 0 Then b = Left(xref, n - 1)

End If

'** key 2004-05-28 addition **
If Left(b, 1) = "'" Then b = Mid(b, 2)

On Error Resume Next
If n > 0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error GoTo 0

End If

If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If
'** end 2004-03-25 changes **
'** end 2004-05-28 changes **

pull = Evaluate(xref)

If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp ' immediate clean-up at this point

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro

On Error Resume Next 'now clean-up can wait

n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)

Else
For Each C In r
C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1))
Next C

pull = r.Value

End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If

End Function
This is sitting in a module in VBA. How do I go about using this for my issue. I understand that it will be able to pull from a closed work book and I have seen it used in conjunction with a VLOOKUP command.

Can you help how I would get these two to work together.

May thanks for your help

Neil

 

[Derek Brown]

I have never used PULL in my code but having watched the Podcast mentioned in my original reply, I would expect that all you have to do is enter the following in a worksheet cell:

=PULL("'\\Brnwcfnp01\Norwich Shared Area\Site\General\CSD Self Reliability\2008-2009\Week "&D2&"\[Filler11_Wk10.xls]Wkly Downtime Sum'!T11")