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

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

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

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