Matching entered cell with named range and copying the list

**taibe** · 01-23-2013, 10:12 AM

Hi All,

Im stuck on a formula and wondering if anyone can help me.

The user enters all the staff names in different properties in the 'Staff' worksheet, naming the each column XXXCol. In the 'Results' worksheet, depending on the user selection in B2, the staff names relating to the property is copied into the worksheet.

Results.xlsx

If any can help, it would be most grateful.

**benishiryo** · 01-23-2013, 10:21 AM

hi taibe, welcome to the forum. say you want your results from D2 onwards, then try:
=IF(ROWS(D$2:D2)>COUNTA(INDIRECT($B$2&"Col")),"",INDEX(INDIRECT($B$2&"Col"),ROWS(D$2:D2)))

copy down as many cells as you desire

**taibe** · 01-24-2013, 03:09 AM

Hi Benishiroyo,

Thanks, just what I wanted.

Another off the topic question, I have created a dynamic range chart using a offset formula, works great with cells either blank or inputted data. However, it goes crazy when using the above formula. Anyway to get round this?

Thanks in advance

**taibe** · 01-24-2013, 03:10 AM

Hi Benishiroyo,

Thanks, just what I wanted.

Another off the topic question, I have created a dynamic range chart using a offset formula, works great with cells either blank or inputted data. However, it goes crazy when using the above formula. Anyway to get round this?

Thanks in advance