|
#VALUE! error
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? |
#VALUE! error
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,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? |
#VALUE! error
I want the value to reed "2000.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,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? |
#VALUE! error
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,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? |
#VALUE! error
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 - |
#VALUE! error
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 - |
#VALUE! error
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 - |
#VALUE! error
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 - |
| All times are GMT +1. The time now is 04:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com