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 -