To address a column in the active sheet of the active book, you can use the col( ) function. For example, col(my long column name), where "my long column name" is the long name. The col( ) function can take either the column's long name or short name. To address a specific cell, use [ ] brackets after the col( ). For example, col(B)[5]. (You could also use the cell(i,j) function.)
If you have two sheets in a book, then the dataset names in sheet1 would be book1_a and book1_b, and the dataset names in sheet2 would be book1_a@2 and book1_b@2. Note that creation order of sheets is what is used in this naming notation.
The following substitution notation is also supported: %([Book1]sheet2, col3, row4). In previous versions, we supported the following: %(wksName, col, row). Here are some examples:
%(book1,2,3) is the active sheet of book1, column2, row 3
%([book1]sheet2, 2, 3) is sheet2 of book1, column2 row 3
Lastly, you can use the new range notation: [page long name]"sheet long name"!col(long name). For example:
range r1 = [book1]sheet1!col(a), r2=[book1]sheet2!col(a)
r2 = r1
Note: You cannot do the following: [book1]sheet2!col(a) = [book1]sheet1!col(a) // need to define range object and then do assignment as above