Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on the definition of refer to
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. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on the definition of refer to
Hello efi
Your name will refer to =OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A)-1) HTH Regards Pascal "efi" a écrit dans le message de ... 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. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on the definition of refer to
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 efi wrote in message ... 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. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on the definition of refer to
1ox all!
------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum a column within a name definition | Excel Discussion (Misc queries) | |||
f statistic, p value definition | Excel Worksheet Functions | |||
$ definition | Excel Worksheet Functions | |||
End of data definition | Excel Discussion (Misc queries) | |||
The definition of.... | Excel Worksheet Functions |