Drop down list returns variable number of values without blanks

**simmo86** · 03-31-2010, 08:10 AM

I have a drop down list for a number of departments and it will return all the users in this department. The only issue is that some have many more than others so entering a row of text underneath means that it can be located way down the page with a lot of empty rows. Is there a way that the text will move down depending on how many users are returned? i.e. the easiest way that springs to mind is if all the users were to be returned in one cell below one another and the cell would expand accordingly.

Anyway, any help would be much appreciated.

Thanks in advance,

Alex

p.s. workbook is attached

**ratcat** · 03-31-2010, 08:19 AM

G'day

Try this example at B8 and copy drag down

Please Login or Register  to view this content.

Cheers

RC

**simmo86** · 03-31-2010, 08:44 AM

Hi ratcat

Thanks very much for that, if I were to change the data to columns rather than rows and with the departments listed a number of times, (as attached) would I just switch the match function with the row() and then put column ()-5 ? Sorry, I am not very familiar why the - 5 is in there? Sorry, It worked really well when I did the rows!

Thanks

Alex

**ratcat** · 03-31-2010, 09:15 AM

G'day Alex,

Okay help with the inquire of ROW()-5.

Example

Original formula at B8
INDEX(Sheet2!$A$3:$U$25,MATCH($B$5,Departments,FALSE),3)

Place the following formula in row 8 doesn't matter what column. =ROW()
and it will return the value of 8.
For the reason of putting -5 next to the embedded formula ROW() so it returns the number 3 (8-5).

Its one way of making a dynamic number without having to re enter the correct Col_number in the Index formula after copy drag.

And now since you wish to go columns instead of row you can apply the =COLUMN() the same way. Remember that,,, that part of the formula is returning the right number for the Index formula.

Well I'm off to bed its past pumpkin o'clock

Cheers

RC