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

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

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

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

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

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

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

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

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