Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
identical formula returns #VALUE! error
Hi,
I have two identical formaulas in a worksheet that refer to the same cells in other worksheets. The first one works properly and returns the correct response. The second one will only return the correct response when the cells are blank otherwise it returns a #VALUE! error even though the first formula returns the correct response. Has anyone experienced this before? Both formula cells are formatted as General. Here is the formula entered in two different cells: =IF('6. Micro Controller & Panel'!A12=0,"NOT STARTED",IF('6. Micro Controller & Panel'!A12='2. Access Control System (ACS)'!J31:J33,"COMPLETED","VERIFY # ENTERED IN 2.06")) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
identical formula returns #VALUE! error
copy the formula into the FX box and not on the worksheet. if you look at
the results of the copied cells you will find the addresses are not the same. You didn't use dollar signs in the formula to make the lookup range absolute. "Tasso" wrote: Hi, I have two identical formaulas in a worksheet that refer to the same cells in other worksheets. The first one works properly and returns the correct response. The second one will only return the correct response when the cells are blank otherwise it returns a #VALUE! error even though the first formula returns the correct response. Has anyone experienced this before? Both formula cells are formatted as General. Here is the formula entered in two different cells: =IF('6. Micro Controller & Panel'!A12=0,"NOT STARTED",IF('6. Micro Controller & Panel'!A12='2. Access Control System (ACS)'!J31:J33,"COMPLETED","VERIFY # ENTERED IN 2.06")) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
identical formula returns #VALUE! error
Hi Joel,
It did not work. I copied and pasted in the fx box and added $ to range. =IF('7. Digital Video Recorder (DVR)'!$A$12=0,"NOT STARTED",IF('7. Digital Video Recorder (DVR)'!$A$12='3. Closed Circuit TV (CCTV)'!$J$23:$J$25,"COMPLETED","VERIFY # ENTERED IN 3.04")) If I unmerge the range and compare single cells it works. "Joel" wrote: copy the formula into the FX box and not on the worksheet. if you look at the results of the copied cells you will find the addresses are not the same. You didn't use dollar signs in the formula to make the lookup range absolute. "Tasso" wrote: Hi, I have two identical formaulas in a worksheet that refer to the same cells in other worksheets. The first one works properly and returns the correct response. The second one will only return the correct response when the cells are blank otherwise it returns a #VALUE! error even though the first formula returns the correct response. Has anyone experienced this before? Both formula cells are formatted as General. Here is the formula entered in two different cells: =IF('6. Micro Controller & Panel'!A12=0,"NOT STARTED",IF('6. Micro Controller & Panel'!A12='2. Access Control System (ACS)'!J31:J33,"COMPLETED","VERIFY # ENTERED IN 2.06")) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
identical formula returns #VALUE! error
Merged cells won't work with offsets. For example
Cells A1:A2 are merged. Offset one row from A1 will give row 3 not row 2. 1) don't use merge cells 2) Perform your look up on cells that aren't merged. "Tasso" wrote: Hi Joel, It did not work. I copied and pasted in the fx box and added $ to range. =IF('7. Digital Video Recorder (DVR)'!$A$12=0,"NOT STARTED",IF('7. Digital Video Recorder (DVR)'!$A$12='3. Closed Circuit TV (CCTV)'!$J$23:$J$25,"COMPLETED","VERIFY # ENTERED IN 3.04")) If I unmerge the range and compare single cells it works. "Joel" wrote: copy the formula into the FX box and not on the worksheet. if you look at the results of the copied cells you will find the addresses are not the same. You didn't use dollar signs in the formula to make the lookup range absolute. "Tasso" wrote: Hi, I have two identical formaulas in a worksheet that refer to the same cells in other worksheets. The first one works properly and returns the correct response. The second one will only return the correct response when the cells are blank otherwise it returns a #VALUE! error even though the first formula returns the correct response. Has anyone experienced this before? Both formula cells are formatted as General. Here is the formula entered in two different cells: =IF('6. Micro Controller & Panel'!A12=0,"NOT STARTED",IF('6. Micro Controller & Panel'!A12='2. Access Control System (ACS)'!J31:J33,"COMPLETED","VERIFY # ENTERED IN 2.06")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX formula returns #REF! error for ranges larger than two cells | Excel Worksheet Functions | |||
Array Formula returns error but only for specific columns | Excel Discussion (Misc queries) | |||
Sumproduct returns value error when ref is formula | Excel Discussion (Misc queries) | |||
Formula to sum every 4th cell returns #DIV/0! error in some column | Excel Worksheet Functions | |||
formula returns error in version 2003 only | Excel Worksheet Functions |