| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#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 - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#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 - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
| Error: "Excel encountered an error and had to remove some formatti | Excel Discussion (Misc queries) | |||
| Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
| I have Error 1919 Error Configuring ODBC dataSource Database | Excel Discussion (Misc queries) | |||
Hybrid Mode
