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

[djames2007]

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

 

[Jacob Skaria]

Use INDIRECT()

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

If this post helps click Yes

 

[Don Guillett]

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

 

[Bernard Liengme]

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

 

[Dave Peterson]

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]

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

 

[djames2007]

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

 

[Dave Peterson]

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

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

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.

 

[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.

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

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