Change to formulae

E

Emma Hope

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 are:
='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
 
E

Emma Hope

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 said:
check out the indirect() function. I think it will do exactly what yoiu want.

Emma Hope said:
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 are:
='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
Click to expand...
Click to expand...
 
H

Harlan Grove

...
....
. . . 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 are:
='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.
Click to expand...

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/micr...tions/msg/ac443753560f0075?dmode=source&hl=en

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