Return Unquie Values

[Guest]

It returned 0

....

So are they just dates or dates with times but formatted to show only
the date portion? Check using the formula

=SUMPRODUCT(SIGN(MOD(H$20:H$44,1)))

If this returns > 0, then there are dates with times in H20:H44. If
you want to treat them as dates alone, change all references to H$2:H
$44 to INT(H$2:H$44).

 

[Guest]

Harlan,

Funny thing is, that when I evaluate the formula, it says that the formula
result is the correct result that it should be, when I exit out of the
formula audit, it goes to the wrong result, the first entry in the range.

 

[Harlan Grove]

Funny thing is, that when I evaluate the formula, it says that
the formula result is the correct result that it should be, when
I exit out of the formula audit, it goes to the wrong result,
the first entry in the range.

....

That makes it appear that you're not entering the formula as an array
formula. Hold down [Ctrl] and [Shift] keys before pressing [Enter].

 

[Guest]

No, I've done the cntrol shift enter thing. Ithink we have broken excel with
these formulas. LOL. I wont give up with this one, something has to give.

Funny thing is, that when I evaluate the formula, it says that
the formula result is the correct result that it should be, when
I exit out of the formula audit, it goes to the wrong result,
the first entry in the range.

....

That makes it appear that you're not entering the formula as an array
formula. Hold down [Ctrl] and [Shift] keys before pressing [Enter].

 

[Guest]

Harlan, here is a screenshot of the formula and a sample spreadsheet.

http://picasaweb.google.com/JavierDiaz4/Excel/photo#5096489505146200818

There I have cloned the formula side by side so that you can see that it
indicates March 2007 seven twice where it should read March then February.
And it the evaluation of the formula, it indicates that the result will be
February, but thats not the outcome.

Funny thing is, that when I evaluate the formula, it says that
the formula result is the correct result that it should be, when
I exit out of the formula audit, it goes to the wrong result,
the first entry in the range.

....

That makes it appear that you're not entering the formula as an array
formula. Hold down [Ctrl] and [Shift] keys before pressing [Enter].

 

[Harlan Grove]

Alright Harlan, sorry to take up your time. This actually the lookup
formula for those dates -

=INDEX(Sheet1!$H$1:$N$1,,MATCH(TRUE,OFFSET(Sheet1!$H$1:$N$1,
MATCH(A1,Sheet1!$G$1:$G$6479,0)-1,)<>"",0))

It seems this formula is making your formula go crazy for some reason.
I'm looking into it.

....

Is there any particular reason you're skipping the 2nd arg in the first
INDEX call rather than just using 2 arguments?

Since Sheet1!H1:N1 would be dates, so numbers, try this formula instead.

=N(INDEX(Sheet1!$H$1:$N$1,MATCH(TRUE,INDEX(Sheet1!$H$1:$N$6479,
MATCH(A1,Sheet1!$G$1:$G$6479,0),0)<>"",0)))

If this doesn't work, what's in A1 and Sheet1!G1:G6479?

 

[Guest]

Harlan, I want to start off by thanking you a million times for your help.
I dont know how I can repay you. That formula you adjusted for me worked
like a charm. It's truely unbelievable that you can do such a thing. Wow,
wow, wow, wow. The problem that I find so far, is that even though I get the
results I need, I cant seem to get around to understanding the formulas most
of the time, lol. I'll try to figure this one out. But Harlan, thanks a
million.

You here that Microsoft, Harlan rocksssssssssssss!

 

[Harlan Grove]

I want to start off by thanking you a million times for your help.

....

You're welcome.

You here that Microsoft, . . .

....

From these newsgroups, MSFT hears nothing. Warning: rant coming.

MSFT has no excuse for your original formula

=INDEX(Sheet1!$H$1:$N$1,,MATCH(TRUE,OFFSET(Sheet1!$H$1:$N$1,
MATCH(A1,Sheet1!$G$1:$G$6479,0)-1,)<>"",0))

and my revision to it

=N(INDEX(Sheet1!$H$1:$N$1,MATCH(TRUE,INDEX(Sheet1!$H$1:$N$6479,
MATCH(A1,Sheet1!$G$1:$G$6479,0),0)<>"",0)))

producing different results.

The first returns something like a range reference to a single cell, and the
second returns that cell's value. If that cell were K1, the simple reference
=K1 would have behaved the same as the result of the SECOND formula, i.e.,
it would have worked with your other formulas.

MSFT has NEVER provided a public explanation for the precise data type your
first formula returns. The 2 commas in sequence, producing a default 0 value
for the 2nd arg to INDEX makes INDEX return something that's not directly
usable. It's either not quite a range reference, or it's just a little more
than a range reference. Whichever, MSFT seems uninterested in documenting
what it is. So the rest of us (possibly excepting the MVPs, who may even be
subject to NDAs about @#$% like this) are left to speculate and to learn
that there are times one must wrap expressions in seemingly useless N, T or
CELL("Contents",..) calls.

Perhaps if the MVPs aren't subject to NDAs about this they could ask someone
on the Excel development team to explain the data type your original formula
returns, that is, if there's anyone on the Excel development team who knows.