='[master workbook.xls]3108'!K25 trying to use value of cell for 3
 

This may be confusing but we have a detail workbook for each job (3107,3108,
3109, ...) each detail workbook has cells tied to the master workbook (the
3108 is the sheet name in the master workbook). When I start a new job I use
the workbook from the last job and resave it as the new job I have the job
(3107,3108,3109,...) entered in cell a1 how can I use the value of cell a1 in
place of the 3108 in my formula(s) so I only change it in cell a1 (one time)
Any help would be appreciated it would be a big time saver for me

='[master workbook.xls]3108'!K25 trying to use value of cell for 3
 

Use INDIRECT()

=indirect(A1 & "!a2") will return the value of a2 from Sheet mentioned in A1

If this post helps click Yes
---------------
Jacob Skaria


"djames2007" wrote:

This may be confusing but we have a detail workbook for each job (3107,3108,
3109, ...) each detail workbook has cells tied to the master workbook (the
3108 is the sheet name in the master workbook). When I start a new job I use
the workbook from the last job and resave it as the new job I have the job
(3107,3108,3109,...) entered in cell a1 how can I use the value of cell a1 in
place of the 3108 in my formula(s) so I only change it in cell a1 (one time)
Any help would be appreciated it would be a big time saver for me

='[master workbook.xls]3108'!K25 trying to use value of cell for 3
 

Try recording a macro to saveAS. Then modify to reference your range.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"djames2007" wrote in message
...
This may be confusing but we have a detail workbook for each job
(3107,3108,
3109, ...) each detail workbook has cells tied to the master workbook (the
3108 is the sheet name in the master workbook). When I start a new job I
use
the workbook from the last job and resave it as the new job I have the job
(3107,3108,3109,...) entered in cell a1 how can I use the value of cell a1
in
place of the 3108 in my formula(s) so I only change it in cell a1 (one
time)
Any help would be appreciated it would be a big time saver for me

='[master workbook.xls]3108'!K25 trying to use value of cell for 3
 

I have two books called 1234.xls and 1235.xls
In a new workbook, in A1 I enter 1234
In B1 I use the formula =[1234.xls]Sheet2!$B$1
This returns the value from B1 of Sheet1 in the file 1234.xls
I can replace the formula by =INDIRECT("["&A1&".xls]Sheet2!$B$1") and get
the same result
If I type 1235 in A1, I get the value from the other workbook
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"djames2007" wrote in message
...
This may be confusing but we have a detail workbook for each job
(3107,3108,
3109, ...) each detail workbook has cells tied to the master workbook (the
3108 is the sheet name in the master workbook). When I start a new job I
use
the workbook from the last job and resave it as the new job I have the job
(3107,3108,3109,...) entered in cell a1 how can I use the value of cell a1
in
place of the 3108 in my formula(s) so I only change it in cell a1 (one
time)
Any help would be appreciated it would be a big time saver for me

='[master workbook.xls]3108'!K25 trying to use value of cell for 3
 

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.

djames2007 wrote:

This may be confusing but we have a detail workbook for each job (3107,3108,
3109, ...) each detail workbook has cells tied to the master workbook (the
3108 is the sheet name in the master workbook). When I start a new job I use
the workbook from the last job and resave it as the new job I have the job
(3107,3108,3109,...) entered in cell a1 how can I use the value of cell a1 in
place of the 3108 in my formula(s) so I only change it in cell a1 (one time)
Any help would be appreciated it would be a big time saver for me

--

Dave Peterson

='[master workbook.xls]3108'!K25 trying to use value of cell f
 

I tried '[master workbook.xls]indirect(a1)'!$F$12 and get invalid references
not sure what to change

"Dave Peterson" wrote:

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.

djames2007 wrote:

This may be confusing but we have a detail workbook for each job (3107,3108,
3109, ...) each detail workbook has cells tied to the master workbook (the
3108 is the sheet name in the master workbook). When I start a new job I use
the workbook from the last job and resave it as the new job I have the job
(3107,3108,3109,...) entered in cell a1 how can I use the value of cell a1 in
place of the 3108 in my formula(s) so I only change it in cell a1 (one time)
Any help would be appreciated it would be a big time saver for me

--

Dave Peterson

='[master workbook.xls]3108'!K25 trying to use value of cell for 3
 

thanks for your help I changed it to the following and it is working
=INDIRECT("'[master workbook.xls]"&A1&"'!$F$12")

"djames2007" wrote:

This may be confusing but we have a detail workbook for each job (3107,3108,
3109, ...) each detail workbook has cells tied to the master workbook (the
3108 is the sheet name in the master workbook). When I start a new job I use
the workbook from the last job and resave it as the new job I have the job
(3107,3108,3109,...) entered in cell a1 how can I use the value of cell a1 in
place of the 3108 in my formula(s) so I only change it in cell a1 (one time)
Any help would be appreciated it would be a big time saver for me

='[master workbook.xls]3108'!K25 trying to use value of cell f
 

Did you download Laurent's addin?

After you do that and install it, try a formula like:

=indirect.ext("'C:\My Documents\excel\[master workbook.xls]" & A1 & "'!$A$1")

(untested)

(Youll need to supply the correct path, too.)



djames2007 wrote:

I tried '[master workbook.xls]indirect(a1)'!$F$12 and get invalid references
not sure what to change

"Dave Peterson" wrote:

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.

djames2007 wrote:

This may be confusing but we have a detail workbook for each job (3107,3108,
3109, ...) each detail workbook has cells tied to the master workbook (the
3108 is the sheet name in the master workbook). When I start a new job I use
the workbook from the last job and resave it as the new job I have the job
(3107,3108,3109,...) entered in cell a1 how can I use the value of cell a1 in
place of the 3108 in my formula(s) so I only change it in cell a1 (one time)
Any help would be appreciated it would be a big time saver for me

--

Dave Peterson


--

Dave Peterson

='[master workbook.xls]3108'!K25 trying to use value of cell for 3
 

As long as that sending workbook is open, you should be happy.

But as soon as you close the "master workbook.xls" and excel recalculates, you
may not be.

djames2007 wrote:

thanks for your help I changed it to the following and it is working
=INDIRECT("'[master workbook.xls]"&A1&"'!$F$12")

"djames2007" wrote:

This may be confusing but we have a detail workbook for each job (3107,3108,
3109, ...) each detail workbook has cells tied to the master workbook (the
3108 is the sheet name in the master workbook). When I start a new job I use
the workbook from the last job and resave it as the new job I have the job
(3107,3108,3109,...) entered in cell a1 how can I use the value of cell a1 in
place of the 3108 in my formula(s) so I only change it in cell a1 (one time)
Any help would be appreciated it would be a big time saver for me

--

Dave Peterson