u are a real master.......
but please i need to know what follows :
in this formula that you gave me :
{=IF(ROWS(E$5:E5)<=E$4;ADDRESS(INDEX(ROW(rng);SMALL(IF(rng=D$5;ROW(rng)-MIN(ROW(rng))+1);ROWS(E$5:E5)));COLUMN(rng);4);"")}
first question :
can we substitute : -MIN(ROW(rng))+1)
to the following : ROWS(rng) ??......AND HOW THE FORMULA WOULD BE
THEN ?? please show it to me i cant do it
second question :
if i would like to make the lookup value : D5 and D6....i think that the
formula will include TRANSPOSE like you taught me ....but how the formula
will look like then ??
please show it to me
third question:
what does the "4" stands for ?
THANKS FOR YOUR HELP REALLY
APPRECIATED
>can we substitute : -MIN(ROW(rng))+1)
>to the following : ROWS(rng) ??......
No, that won't work.
> second question :
Change the formula in E4 to:
=SUMPRODUCT(COUNTIF(rng,D5:D6))
Change the array formula** in D5 to:
=IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL(IF(ISNUMBER(MATCH(rng,D$5:D$6,0)),ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"")
> third question:
> what does the "4" stands for ?
That's the argument that determines what reference style the result will be
in. 4 means relative so the result is returned without $ signs. It's easier
to read without the $ signs.

Signature
Biff
Microsoft Excel MVP
>u are a real master.......
> but please i need to know what follows :
[quoted text clipped - 95 lines]
>> >> > USE
>> >> > ???
pierre - 25 May 2008 13:02 GMT
One last question :
regarding this formula
{=IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL(IF(ISNUMBER(MATCH(rng,D$5:D$6,0)),ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"")}
CAN WE WRITE IN ANOTHER WAY IN ORDER TO BE SHORTER ???
especially this part : ROW(rng)-MIN(ROW(rng))+1)
THANKS AGAIN
> >can we substitute : -MIN(ROW(rng))+1)
> >to the following : ROWS(rng) ??......
[quoted text clipped - 117 lines]
> >> >> > USE
> >> >> > ???
T. Valko - 25 May 2008 17:59 GMT
You seem to be overly concerned with:
ROW(rng)-MIN(ROW(rng))+1)
You could put this portion in a separate cell and then refer to that cell:
=MIN(ROW(rng))+1
You need to understand what this is doing.
When you index a range the values of that range are in specific positions.
The positions are numbered 1 through the total number of cells in the range.
Your range is C5:C7. So:
C5 = position 1
C6 = position 2
C7 = position 3
In order to get the correct result from the formula we need to tell the
INDEX function we want the value located at position n. We do that using
ROW(rng)-MIN(ROW(rng))+1).
We have to convert the actual row numbers of "rng" to correspond to the
position numbers of the indexed range.
The actual row numbers of "rng" are 5,6,7. We need to convert those to
1,2,3. Here's how we do that:
ROW(rng)-MIN(ROW(rng))+1)
ROW(C5)-MIN(ROW(C5))+1 = 1
ROW(C6)-MIN(ROW(C5))+1 = 2
ROW(C7)-MIN(ROW(C5))+1 = 3
The *only* time you could replace ROW(rng)-MIN(ROW(rng))+1) with just
ROW(rng) is *if* the actual indexed range started in row 1. For example, if
the actual indexed range was A1:A3. In this case the actual row numbers
naturally correspond to the position numbers on the indexed range.
A1 = row 1 = position 1
A2 = row 2 = position 2
A3 = row 3 = position 3
However, if the actual range was A1:A3 and you used just ROW(rng) and
inserted a new row 1 the formula could return incorrect results because now
the row numbers do not correspond to the position numbers of the indexed
range.
Using ROW(rng)-MIN(ROW(rng))+1) accounts for this. This is the most
fool-proof method to convert the actual row numbers to the correct position
numbers.
exp101

Signature
Biff
Microsoft Excel MVP
> One last question :
>
[quoted text clipped - 134 lines]
>> >> >> > USE
>> >> >> > ???
pierre - 25 May 2008 18:09 GMT
THANKS for your time , your patience , and for these clear and important
answers
THANK YOU SIR.......
T. Valko - 25 May 2008 19:32 GMT
You're welcome. Thanks for the feedback!

Signature
Biff
Microsoft Excel MVP
> THANKS for your time , your patience , and for these clear and important
> answers
> THANK YOU SIR.......