I have a set of data in a particular column. I have named a range with offset formulae as follows :
  =OFFSET(Sheet1!$A$2,0,0,total,1)

Note : First row is the heading(not to be included in the range)
and further have attached this range(listfillrange property) to the listbox.

total counts the number of elements in a column.

It works fine otherwise, but if i try to insert a cell just above my first value the value does'nt come in the range but if i insert a cell after my first value it works fine. I believe i m can do it by changing the reference type but not sure how.

Help will be appreciated.

Ajitpal S Padda

__________________
Ajitpal S Padda

Try

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$1:$A$1000 )-1,1)

Ooops not quite right, try this:

=OFFSET(OFFSET(Sheet1!$A$1,1,0),0,0,COUNTA(Sheet1! $A$1:$A$1000)-1,1)

Thanks Maccas!! That was real quick and worked perfect.



Ajitpal S Padda