Problem with drop down and this formula OFFSET(C1,0,0,COUNTA(s:s)-1)

[zmhnk]

Hi ...
i have S columns that get the name of sheets in its as this

Column "S"

Jason
Tom
Brad
Hanks
Jhon

<tbody>
</tbody>

and i have in range("B2") drop down list to pick one of the names in columns "S"

and in drop dwon i have this formula

=OFFSET(C1,0,0,COUNTA(s:s)-1)

<tbody>
</tbody>

but the problem is with this formula in drop down list all names shown except last name ,which in this case is "Jhon"
and i dont know why

**NOTE: the rows under last name are empty

 

[Aladin Akyurek]

Hi ...
i have S columns that get the name of sheets in its as this

Column "S"

Jason
Tom
Brad
Hanks
Jhon

<tbody>
</tbody>

and i have in range("B2") drop down list to pick one of the names in columns "S"

and in drop dwon i have this formula

=OFFSET(C1,0,0,COUNTA(s:s)-1)

<tbody>
</tbody>

but the problem is with this formula in drop down list all names shown except last name ,which in this case is "Jhon"
and i dont know why

**NOTE: the rows under last name are empty

Try...

Assuming that C2 houses the first of the relevant names on Sheet1 and the range do not house formula blanks, try...

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,MATCH(REPT("z",255),Sheet1!$C:$C))

If you insist on OFFSET...

=OFFSET(Sheet1!$C$2,0,0,MATCH(REPT("z",255),Sheet1!$C:$C)-SUM(ROW(Sheet1!$C$2))+1)

 

[zmhnk]

Thnak you very mush its good and work as i want, but coz iam learning excel and vba more and more may i ask you for something
just explain me how this formula work, I know its running perfectly but how for learning purpose ???

 

[Aladin Akyurek]

Thnak you very mush its good and work as i want,

You are welcome.

but coz iam learning excel and vba more and more may i ask you for something
just explain me how this formula work, I know its running perfectly but how for learning purpose ???

The one with INDEX says: From cell[1]to the cell[2] which houses the last text value. Put otherwise, INDEX(Range,N) means both the Nth value in Range and the address of the Nth value. Note that Cell[1] is C2.

The one with OFFSET says: The range of interest consists of M cells in the same column as the column of oth start cell[1]. M is specified as the row of the last cell minus the row of the start cell plus 1.

Note also that MATCH(REPT("z",255),Reference) returns the row/position of the last text value in Reference. Its working is analogical to the working of MATCH(9.99999999999999E+307,Reference), a kindred expression. The explanation is given in post #7 of:\

http://www.mrexcel.com/forum/showthread.php?310278