Change to formulae
 

Hi All,

i'm hoping this is a pretty easy question, i just cannot get my head around
it. I have a number of formulae that refer to another workbook, however they
all refer to different worksheets within that workbook. Each month i have to
change the formulae to reflect a new workbook. What i would like to do is
have the formulae refer to a cell on my spreadsheet and then that cell
contains the name of the folder and workbook.

For example, my formulae a
='J:\GFPS\Revenue Figures\Aug 05 Rev\[30.08.05.xls]Reconciliation'!$J$15
=(VLOOKUP(P22,'J:\GFPS\Revenue Figures\Aug 05 Rev\[30.08.05.xls]Monthly
Summary'!$C$27:$Y$39,15,FALSE)

but i would like cell P33 to contain Aug 05 Rev\[30.08.05] and then replace
this part of the formulae to refer to cell P33. Then when the month changes
to spetember, i just need to change cell P33 to Sep 05 Rev\[30.09.05] and all
the formulae will change to reflect this.

Thanks
Emma

check out the indirect() function. I think it will do exactly what yoiu want.

"Emma Hope" wrote:

Hi All,

i'm hoping this is a pretty easy question, i just cannot get my head around
it. I have a number of formulae that refer to another workbook, however they
all refer to different worksheets within that workbook. Each month i have to
change the formulae to reflect a new workbook. What i would like to do is
have the formulae refer to a cell on my spreadsheet and then that cell
contains the name of the folder and workbook.

For example, my formulae a
='J:\GFPS\Revenue Figures\Aug 05 Rev\[30.08.05.xls]Reconciliation'!$J$15
=(VLOOKUP(P22,'J:\GFPS\Revenue Figures\Aug 05 Rev\[30.08.05.xls]Monthly
Summary'!$C$27:$Y$39,15,FALSE)

but i would like cell P33 to contain Aug 05 Rev\[30.08.05] and then replace
this part of the formulae to refer to cell P33. Then when the month changes
to spetember, i just need to change cell P33 to Sep 05 Rev\[30.09.05] and all
the formulae will change to reflect this.

Thanks
Emma

I should have said, I have tried the indirect formula and cannot get it to
work. Please can you show me exactly how to use it with the formulae below.

I have tried:
='J:\GFPS\Revenue Figures\ & Indirect(P33) & Reconciliation'!$J$15
without success


"bj" wrote:

check out the indirect() function. I think it will do exactly what yoiu want.

"Emma Hope" wrote:

Hi All,

i'm hoping this is a pretty easy question, i just cannot get my head around
it. I have a number of formulae that refer to another workbook, however they
all refer to different worksheets within that workbook. Each month i have to
change the formulae to reflect a new workbook. What i would like to do is
have the formulae refer to a cell on my spreadsheet and then that cell
contains the name of the folder and workbook.

For example, my formulae a
='J:\GFPS\Revenue Figures\Aug 05 Rev\[30.08.05.xls]Reconciliation'!$J$15
=(VLOOKUP(P22,'J:\GFPS\Revenue Figures\Aug 05 Rev\[30.08.05.xls]Monthly
Summary'!$C$27:$Y$39,15,FALSE)

but i would like cell P33 to contain Aug 05 Rev\[30.08.05] and then replace
this part of the formulae to refer to cell P33. Then when the month changes
to spetember, i just need to change cell P33 to Sep 05 Rev\[30.09.05] and all
the formulae will change to reflect this.

Thanks
Emma

"Emma Hope" wrote...
....
. . . I have a number of formulae that refer to another workbook,
however they all refer to different worksheets within that workbook.
Each month i have to change the formulae to reflect a new workbook.
What i would like to do is have the formulae refer to a cell on my
spreadsheet and then that cell contains the name of the folder and
workbook.

For example, my formulae a
='J:\GFPS\Revenue Figures\Aug 05 Rev\[30.08.05.xls]Reconciliation'!$J$15
=(VLOOKUP(P22,'J:\GFPS\Revenue Figures\Aug 05 Rev\[30.08.05.xls]Monthly
Summary'!$C$27:$Y$39,15,FALSE)

but i would like cell P33 to contain Aug 05 Rev\[30.08.05] and then
replace this part of the formulae to refer to cell P33. Then when the
month changes to spetember, i just need to change cell P33 to
Sep 05 Rev\[30.09.05] and all the formulae will change to reflect
this.

If you're using full pathnames, these other workbooks wouldn't be open.
Excel's INDIRECT function only works with open workbooks.

Your alternatives are given in the following archived posting.

http://groups.google.com/group/micro...e=source&hl=en

(or http://makeashorterlink.com/?G13632DBB ).