dinamic path name

[emilija]

Hi,
Here is a basic description of what I want to do

I have a file "SOURCEFILE" with 30 sheets , sheets names are different
numbers, e.g. 101, 102..112, 202....
all sheets have same structure

other workbook "DESTFILE" take data from this sheets
ex: one formula in DESTFILE is : ='[SOURCEFILE.xls]112'!$G$364

what I would like to do is the following: when the number in one cell eg A1
(input cell for number of the sheet) is changed than formula path to changes
also,
ex: if I input in A1 101, the above formula to become
='[SOURCEFILE.xls]101'!$G$364

tx in advance
Emilija

 

[Bob Phillips]

=INDIRECT("'[SOURCEFILE.xls]"&A1&"'!$G$364")

but this only works with the other workbook being open.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Garage YaKa]

Obtain a Cell or Range From WorkBook Close

http://cjoint.com/?eCwl7ScBRi

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" And Target.Count = 1 Then
Chemin = ActiveWorkbook.Path
SourceFile = "SourceFile.xls"
Sheet = Target
RangeRead = "G364"
Target.Offset(0, 2).Formula = "='" & Chemin & "\[" & SourceFile &
"]" & Sheet & "'!" & RangeRead
'Target.Offset(0, 2).Value = Target.Offset(0, 2).Value ' Value only
End If
End Sub

Cordialy JB

 

[don]

Obtain a Cell or Range From WorkBook Close

http://cjoint.com/?eCwl7ScBRi

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" And Target.Count = 1 Then
Chemin = ActiveWorkbook.Path
SourceFile = "SourceFile.xls"
Sheet = Target
RangeRead = "G364"
Target.Offset(0, 2).Formula = "='" & Chemin & "\[" & SourceFile &
"]" & Sheet & "'!" & RangeRead
'Target.Offset(0, 2).Value = Target.Offset(0, 2).Value ' Value only
End If
End Sub

Cordialy JB

Hi I have a similar need for this but don't understand VBA at all. I
have had a go and used your code and it seems to work fine thank you.
My question is how would this be expanded to include data in a range of
cells say A2:d20

Many thanks


Don