Offset Dynamic Named Range

Hi all,

I know how to create a dynamic named range so that no blanks appear in my data validation list but have only ever done this for a list in a column, i.e...

-----A-----
1--Apple
2--Orange
3--Pear
4--Bannana
5--
6--

A1:A6 would be named Fruit, then name manager, edit: =OFFSET($A$1,0,0,COUNT($A1:$A6),1)

The Data Validation list then shows Apple, Orange, Pear, Bannana. And if I add Peach into A5 later, that then appears in the list.


What I need though is to edit this as if the named range 'Fruit' covers cells A1:F1.

------A---------B-------C--------D-----------E-----------F
1--Apple---Orange---Pear---Bannana---
2--


Thanks in advance.

Jason

Originally Posted by TMS

This is the problem when the information provided is over-simplified and does not reflect the real workbook.

Please post a sample workbook which has typical data and formulae in the cells/rows/columns that you need to cater for.

Not interested in it being real data, just enough that it can be tested.

Example workbook attached that reflects the real data better.

Thanks again,

Jason

Lovely. Works just like I hoped.

Thanks very much TMS!!