LOOKUP is delivering inaccurate data

[Guest]

Is there a way for me to see what cells within a workbook, or worksheet, are
referencing a specific cell?

I had all data on one worksheet, then I cut 1/2 of it and pasted it into
another worksheet and consequently one cell in the original worksheet is now
returning inaccurate info.

Any assistance would be greatly appreciated.

Tom

 

[Guest]

Select the cell
Press the F5 key
Click on Special
Click on 'Dependents'
Click on OK

 

[Guest]

That worked fine, but I still can't solve the underlying problem.

The formula is such: =LOOKUP(BG270,BG10:BG$267,B10:B$267)

Line 270, Column B should display what is in B171, but it isn't. Instead it
is going up the sheet and displaying information that isn't asked for in the
formula.

FYI: Line
146| 139 7/21/06 5.92


A B BG
169| 162 8/23/06 62.29
170| 163 8/24/06 61.03
171| 164 8/25/06 40.13
172| 165 8/28/06 65.77

268| 162 8/23/06 62.29
269| 163 8/24/06 61.03
270| 139 7/21/06 40.13
271| 165 8/28/06 65.77

Any suggestions?

Thanks,

Tom

 

[Guest]

.. The formula is such: =LOOKUP(BG270,BG10:BG$267,B10:B$267)

Believe the lookup vector range: BG10:BG$267
needs to be sorted in ascending order

---

 

[Guest]

From the Help file re: LOOKUP()
---------------------------------------------------------------
Lookup_vector is a range that contains only one row or one column. The
values in lookup_vector can be text, numbers, or logical values.

Important The values in lookup_vector must be placed in ascending order:
....,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give
the correct value. Uppercase and lowercase text are equivalent.

 

[Guest]

BG is chronological data inputed based off of B...the current date.

The formula worked fine before I cut 1/2 of the data and pasted it to
another worksheet within the workbook.

I'm stumped.

Tom

 

[Guest]

Unless your example data is wrong, column BG really IS NOT SORTED

FYI: Line
146| 139 7/21/06 5.92


A B BG
169| 162 8/23/06 62.29
170| 163 8/24/06 61.03
171| 164 8/25/06 40.13
172| 165 8/28/06 65.77

268| 162 8/23/06 62.29
269| 163 8/24/06 61.03
270| 139 7/21/06 40.13
271| 165 8/28/06 65.77

 

[Guest]

You're correct. BG is not sorted. B & BG is input daily and need to be in
the order they are entered so to activate other formulas in other cells.

However, here is an interesting discovery: I have random numbers in
BG111:BG172 ranging from .02 - 100. When I go to BG 173 and enter a number
less than 24 I get 7/17/06, my the result of my LOOKUP; if between 25 - 40 I
get 7/21/06; if over 41 I get the proper (current) date, which in this case
would be 08/29/06.

This leads me to believe there is a programming glitch related to the LOOKUP
function. Almost to look like I am asking IF <24,7/17/06, IF>24<40, 7/21/06,
IF >=41,Today().

And all of this started happening after I moved part of the worksheet to
another page within the workbook.

Any further suggestions?

Tom