Sep 22 2017
07:53 AM
- last edited on
Jul 25 2018
10:06 AM
by
TechCommunityAP
Sep 22 2017
07:53 AM
- last edited on
Jul 25 2018
10:06 AM
by
TechCommunityAP
I am creating a workbook with multiple formula refrences to other sheets within the workbook (no external refrences) and for some reason the "cell range 1 = cell range 2 on another sheet" is showing either a value error or is simply displaying the formula ='Customer Data'!BI4:BQ4 as text. I have changed the format type with no luck. I have also removed the range of cells and replaced with cell to cell. I have other formulas of the same type that are working in my workbook. Any ideas?
Sep 22 2017 08:09 AM
Hi Kathy,
One of the possible reasons - if you add the formula into the cell which was formatted as text, the formula will be shown as text. If you change cell format after that that won't help itself, after that you shall enter into the cell edit mode and Enter again.
Sep 22 2017 08:43 AM
Deleted everything from the cell, Set the format to General in both the starting and ending locations and renetered the formual. Still getting the #Value error.
Sep 22 2017 08:54 AM
SolutionOkay, at least that's not the formula as the text. Do you receive the #Value with reference on the single cell?, like
='Customer Data'!BI4
Sep 22 2017 08:57 AM
Single cell worked. Perhaps it was the formating and the single cell compounding on eachother. Thanks for your help!
Sep 22 2017 09:19 AM
No problem. Just in case - if you in the cell the reference on the range in another sheet (have no idea why it could be needed) like
='Customer Data'!BI4:BQ4
when you have #VALUE if insert formula in other than from BI to BQ columns. In above columns it returns the value from the relative column in source sheet. You may check if use absolute references
='Customer Data'!$BI$4:$B$Q4
enter this formula in BH and copy to the right till BR. You'll have two #VALUE at the ends and copy of your BI:BQ in the middle.
Sep 22 2017 11:00 AM
The issue here is not what are you doing? but what are you trying to do?
As far as I've used A1:A3=B1:B3 only will check your first cell and return true or false, so even if you use an array formula won't help as you'll end up checking as many items in the same range as if you'd go single cell with A1=B1.
So if you only want to check if it's the same then single cell will work out fine.
Sep 22 2017 11:21 AM
Carlos, that could make sense in some situations. An reference on the range not necessary returns the first value in the range. For example, if you'd like to make a reference on B1:B3 into D column you may use in D1
=B1
and drag down. Out of you range it returns zero.
Or you may put in D1
=$B$1:$B$3
and drag down. Result will be the same except out of your range you'll have #VALUE.
Is zero in your range or not you are not sure, but with #VALUE you may use some custom made error handling to put something what shall be outside the range.
Sep 22 2017 08:54 AM
SolutionOkay, at least that's not the formula as the text. Do you receive the #Value with reference on the single cell?, like
='Customer Data'!BI4