question on the definition of refer to

  • Thread starter Thread starter efi
  • Start date Start date
E

efi

Hi,
I want to define a name that will indlude all the data in a specific
column except the first cell.
How can I do it?

Currently in my definitioon is written : =Sheet1!$A:$A

I want it to be dynamically, meaning if the user will insert a new row
in this column it will be added to the definition, this is why I
defined the whole column.

Any ideas?

10x.
 
Hello efi
Your name will refer to
=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A)-1)
HTH
Regards
Pascal
 
Hi,
Use the OFFSET function.
I'll assume your worksheet is called Data.
Go to Insert, Name, Define...in the menus. Give your range a name and
in the Refers to box put;
=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A)-1,1)

The range starts in the second cell of column A. The Counta function
assumes there is something in cell A1 to count. If there is nothing in
cell A1 use COUNTA(Data!$A:$A) instead.
Look up the OFFSET function for the meaning of the 0 and 1 parameter
values. Be careful you don't have any blank cells in the middle of
your column, or you will miss that many cells at the bottom of your
range. The Offset function is very flexible for defining ranges
involving multiple columns and ones offset away from A.
regards
Paul
 
Back
Top