In a function, how do I reference values in cells offset from the current
cell? For example, I want to concatenate the text in the cells in the two
rows above the cell with the function call. Note that the cell containing the
function is not necessarily the active cell, so I can't use Activecell to
determine it's row/column. I also haven't been able to figure out how to use
the Offset function to retrieve the information without also using
Activecell. I'm sure this is easy but I just haven't been able to get it.

Thanks in advance.

CM

I expect someone will give you advice on OFFSET
However, I would like to point you towards something different

For j =1 to 10
Cells(1, j) =2*j
Next j

will fill A1:J1 with 2,4,6....

Likewise
j = 4
Cells(j,1)=cells(j-1,1) & cells(j-1,2)

will concatenate A3 and B3 into A4

Just remember it is Cells(row, column); the mnemonic 'RC' might mean
something to you.
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"CuriousMark" wrote in message
...
In a function, how do I reference values in cells offset from the current
cell? For example, I want to concatenate the text in the cells in the two
rows above the cell with the function call. Note that the cell containing
the
function is not necessarily the active cell, so I can't use Activecell to
determine it's row/column. I also haven't been able to figure out how to
use
the Offset function to retrieve the information without also using
Activecell. I'm sure this is easy but I just haven't been able to get it.

Thanks in advance.

CM

Thanks Bernard for the prompt response, but it doesn't really answer my
question. I need to use relative references based on the cell that contains
the formula. That could be any cell and is not necessarilty the Activecell.
Using your second example for instance, the cell references need to be
relative to the cell that contains the formula.


"Bernard Liengme" wrote:

I expect someone will give you advice on OFFSET
However, I would like to point you towards something different

For j =1 to 10
Cells(1, j) =2*j
Next j

will fill A1:J1 with 2,4,6....

Likewise
j = 4
Cells(j,1)=cells(j-1,1) & cells(j-1,2)

will concatenate A3 and B3 into A4

Just remember it is Cells(row, column); the mnemonic 'RC' might mean
something to you.
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"CuriousMark" wrote in message
...
In a function, how do I reference values in cells offset from the current
cell? For example, I want to concatenate the text in the cells in the two
rows above the cell with the function call. Note that the cell containing
the
function is not necessarily the active cell, so I can't use Activecell to
determine it's row/column. I also haven't been able to figure out how to
use
the Offset function to retrieve the information without also using
Activecell. I'm sure this is easy but I just haven't been able to get it.

Thanks in advance.

CM

I think we (at least, I) need a little clarification on your posting. First,
you have posted in the programming newsgroup, so am I correct in assuming we
are talking about VB code? Whether your answer to this question is yes or
no, can you give us an example that shows what you have and what you want in
the as a result?

Rick


"CuriousMark" wrote in message
...
In a function, how do I reference values in cells offset from the current
cell? For example, I want to concatenate the text in the cells in the two
rows above the cell with the function call. Note that the cell containing
the
function is not necessarily the active cell, so I can't use Activecell to
determine it's row/column. I also haven't been able to figure out how to
use
the Offset function to retrieve the information without also using
Activecell. I'm sure this is easy but I just haven't been able to get it.

Thanks in advance.

CM

This is the simple way:

Function curious(x, y)
Application.Volatile
curious = x & " " & y
End Function
It is called with =Curious(A1,B1)

This function 'knows' what cell it is in:

Function curiouser()
Application.Volatile
x = Application.Caller.Offset(0, -2)
y = Application.Caller.Offset(0, -1)
curiouser = x & " " & y
End Function

It is called with =curiouser() with an empty argument list
Either one lets you do things like
If x = "apple" then
.....
end if

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"CuriousMark" wrote in message
...
Gladly. I want to create a function that when I paste it into a cell, will
derive it's value from the cells next to it. Here is an example, which I
realize doesn't require creation of a custom function, but is
illustrative:

A1 = red
A2 = orange
A3 = yellow
A4 = green
A5 = blue

B1 = apple
B2 = mango
B3 = banana
B4 = grape
B5 = moon

Custom Function myFn() entered into C1 produces:

C1 = red apple

If I then copy and paste it into the remaining cells in column C it should
produce this result:

C2 = orange mango
C3 = yellow banana
C4 = green grape
C5 = blue moon

So, I want the function to "know" which cell it is in, and then retrieve
data from the two adjacent cells relative to its location. It would be
simple
to make C1 = A1 & " " & B1, and then copy and paste into the other cells,
but
that's not what I want because I need to do conditional analysis of the
contents of a variable number of adjacent cells.

Thanks.

CM



"Rick Rothstein (MVP - VB)" wrote:

I think we (at least, I) need a little clarification on your posting.
First,
you have posted in the programming newsgroup, so am I correct in assuming
we
are talking about VB code? Whether your answer to this question is yes or
no, can you give us an example that shows what you have and what you want
in
the as a result?

Rick


"CuriousMark" wrote in message
...
In a function, how do I reference values in cells offset from the
current
cell? For example, I want to concatenate the text in the cells in the
two
rows above the cell with the function call. Note that the cell
containing
the
function is not necessarily the active cell, so I can't use Activecell
to
determine it's row/column. I also haven't been able to figure out how
to
use
the Offset function to retrieve the information without also using
Activecell. I'm sure this is easy but I just haven't been able to get
it.

Thanks in advance.

CM

Thanks Bernard. curioser() looks like exactly what I was trying to figure out.

CM

"Bernard Liengme" wrote:

This is the simple way:

Function curious(x, y)
Application.Volatile
curious = x & " " & y
End Function
It is called with =Curious(A1,B1)

This function 'knows' what cell it is in:

Function curiouser()
Application.Volatile
x = Application.Caller.Offset(0, -2)
y = Application.Caller.Offset(0, -1)
curiouser = x & " " & y
End Function

It is called with =curiouser() with an empty argument list
Either one lets you do things like
If x = "apple" then
.....
end if

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"CuriousMark" wrote in message
...
Gladly. I want to create a function that when I paste it into a cell, will
derive it's value from the cells next to it. Here is an example, which I
realize doesn't require creation of a custom function, but is
illustrative:

A1 = red
A2 = orange
A3 = yellow
A4 = green
A5 = blue

B1 = apple
B2 = mango
B3 = banana
B4 = grape
B5 = moon

Custom Function myFn() entered into C1 produces:

C1 = red apple

If I then copy and paste it into the remaining cells in column C it should
produce this result:

C2 = orange mango
C3 = yellow banana
C4 = green grape
C5 = blue moon

So, I want the function to "know" which cell it is in, and then retrieve
data from the two adjacent cells relative to its location. It would be
simple
to make C1 = A1 & " " & B1, and then copy and paste into the other cells,
but
that's not what I want because I need to do conditional analysis of the
contents of a variable number of adjacent cells.

Thanks.

CM



"Rick Rothstein (MVP - VB)" wrote:

I think we (at least, I) need a little clarification on your posting.
First,
you have posted in the programming newsgroup, so am I correct in assuming
we
are talking about VB code? Whether your answer to this question is yes or
no, can you give us an example that shows what you have and what you want
in
the as a result?

Rick


"CuriousMark" wrote in message
...
In a function, how do I reference values in cells offset from the
current
cell? For example, I want to concatenate the text in the cells in the
two
rows above the cell with the function call. Note that the cell
containing
the
function is not necessarily the active cell, so I can't use Activecell
to
determine it's row/column. I also haven't been able to figure out how
to
use
the Offset function to retrieve the information without also using
Activecell. I'm sure this is easy but I just haven't been able to get
it.

Thanks in advance.

CM