Thanks David this works!

No, I didn't look at the help for the CELL function - I don't understand how
this bit works - I copied this off this web site from another post.

"David Biddulph" wrote:

Did you look at Excel help for the CELL function?

"Syntax
CELL(info_type,reference)
...."
"Reference is the cell that you want information about. If omitted,
information specified in info_type is returned for the last cell that was
changed. "

Try changing CELL("filename") to CELL("filename",A1)
--
David Biddulph

"M" wrote in message
...
Thanks Pete, unfortunately I still have the same problem - The formula
works
until I change a cell (even one that has no connection with this formula)
on
the sheet that the INDIRECT formula references. :-(

If I click on the cell with my formula in and then in the formula bar and
then hit return (without making an alterations) the cell reeds correctly
again (2000.01)

"Pete_UK" wrote:

The sheet name (01) is a text value, not a number, so you can't divide
it by 100. You can put VALUE( ) around the part that extracts the
sheet name to convert it to a number, like this:

=INDIRECT(A43&"!M1")+VALUE(MID(CELL("filename"),SE ARCH("]",CELL
("filename"))+1,2Â*55))/100

You might also need to include a TRIM or SUBSTITUTE function in case
you have spaces in the name.

Hope this helps.

Pete

On Nov 25, 2:36 pm, M wrote:
I want the value to reed "2000.01" made up from a cell on another sheet
(2000) and the name of the current sheet (01).



"Bernard Liengme" wrote:
Please tell us what you are trying to accomplish
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"M" wrote in message
...
I am getting a "#VALUE!" error with the following formula...

=INDIRECT(A43&"!M1")+(MID(CELL("filename"),SEARCH( "]",CELL("filename"))+1,2Â*55)/100)

The INDIRECT half of the formula returns "2000" from a sheet called
"C.S.".

The sheet name this formula is on is "01".

I want the value to reed "2000.01".

This formula works however if I alter anything on the "C.S." I get
a
"#VALUE!" error. Can you help?- Hide quoted text -

- Show quoted text -