#VALUE! error

[M]

I am getting a "#VALUE!" error with the following formula...

=INDIRECT(A43&"!M1")+(MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,255)/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?

 

[Bernard Liengme]

Please tell us what you are trying to accomplish

 

[M]

I want the value to reed "2000.01"

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

I am getting a "#VALUE!" error with the following formula...

=INDIRECT(A43&"!M1")+(MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,255)/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?

 

[M]

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).

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

I am getting a "#VALUE!" error with the following formula...

=INDIRECT(A43&"!M1")+(MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,255)/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?

 

[Pete_UK]

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"),SEARCH("]",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

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).

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

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 -

 

[M]

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)

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"),SEARCH("]",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

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).

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

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 -

 

[David Biddulph]

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

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)

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"),SEARCH("]",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

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).



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

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 -

 

[M]

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.

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

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)

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"),SEARCH("]",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

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).



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

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 -