Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 1st 09, 04:12 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2009
Posts: 2
Default 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   Report Post  
Old June 1st 09, 04:25 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,101
Default 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   Report Post  
Old June 1st 09, 04:51 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2009
Posts: 2
Default 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   Report Post  
Old June 1st 09, 04:57 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,101
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
INDEX formula returns #REF! error for ranges larger than two cells hmm Excel Worksheet Functions 3 February 18th 08 01:58 PM
Array Formula returns error but only for specific columns pblenis Excel Discussion (Misc queries) 3 March 9th 07 11:02 PM
Sumproduct returns value error when ref is formula wx4usa Excel Discussion (Misc queries) 3 January 21st 07 04:57 PM
Formula to sum every 4th cell returns #DIV/0! error in some column Brent Excel Worksheet Functions 5 March 16th 06 07:20 PM
formula returns error in version 2003 only Wayne Cameron Excel Worksheet Functions 0 October 31st 05 05:00 PM


All times are GMT +1. The time now is 06:33 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017