dinamic path name

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

=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)

"emilija" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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

Garage YaKa wrote:
> 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