How to Change Reference

[Guest]

I want this formula to auto-update the month name (Tab):

='\\Flrc\shares\Accounting\Hotel Cashier\[Cash ReportWB CY2007.xls]NOVEMBER
07'!$C$6

How can I do this by just changing the contents of one cell that contains
the word "NOVEMBER" or "DECEMBER"?

 

[Peo Sjoblom]

You would need an add-in since the workbook is closed

http://xcell05.free.fr/morefunc/english/index.htm


it has a function called INDIRECT.EXT

read help on how to implement it


--


Regards,


Peo Sjoblom

 

[Bernard Liengme]

I have a workbook called BOOK$ that has a worksheet called NOVEMBER 07
In another workbook I have the text NOVEMBER
This formula will retrieve the values from C6 the first workbook
=INDIRECT("'C:\Documents and Settings\Owner\My
Documents\Central\[Book4.xls]"&A13&" 07'!$C$6")

Note that uppercase is not needed I did this for clarity.
As Peo points out, INDIRECT needs the file open but INDIRECT.EXT does not.
Hope you get it working with this lead in
best wishes

 

[Guest]

I have tried this indirect formula a few times before posting and was getting
"#REF" as the answer. The file is open but not sure what I'm doing wrong. I
am assuming in your example that A13 contains the text "NOVEMBER", right?

I have a workbook called BOOK$ that has a worksheet called NOVEMBER 07
In another workbook I have the text NOVEMBER
This formula will retrieve the values from C6 the first workbook
=INDIRECT("'C:\Documents and Settings\Owner\My
Documents\Central\[Book4.xls]"&A13&" 07'!$C$6")

Note that uppercase is not needed I did this for clarity.
As Peo points out, INDIRECT needs the file open but INDIRECT.EXT does not.
Hope you get it working with this lead in
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

I want this formula to auto-update the month name (Tab):

='\\Flrc\shares\Accounting\Hotel Cashier\[Cash ReportWB
CY2007.xls]NOVEMBER
07'!$C$6

How can I do this by just changing the contents of one cell that contains
the word "NOVEMBER" or "DECEMBER"?

 

[Guest]

I created a similar formula to another spreadsheet and it worked just fine.
The one I am referencing has a password on it and if I have the formula in it
and hit f2 and then enter, I am prompted for a password to update the
formula. But with indirect() I just get #REF. Do you suppose the error is
in the protection being on?

I have a workbook called BOOK$ that has a worksheet called NOVEMBER 07
In another workbook I have the text NOVEMBER
This formula will retrieve the values from C6 the first workbook
=INDIRECT("'C:\Documents and Settings\Owner\My
Documents\Central\[Book4.xls]"&A13&" 07'!$C$6")

Note that uppercase is not needed I did this for clarity.
As Peo points out, INDIRECT needs the file open but INDIRECT.EXT does not.
Hope you get it working with this lead in
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

I want this formula to auto-update the month name (Tab):

='\\Flrc\shares\Accounting\Hotel Cashier\[Cash ReportWB
CY2007.xls]NOVEMBER
07'!$C$6

How can I do this by just changing the contents of one cell that contains
the word "NOVEMBER" or "DECEMBER"?