Generic adjacent cell reference

[Paul]

This should be simple but I am not finding it in the docs...

I have a formula which I want should always refer to the adjacent cell.
For instance The data is in A1 and I put the formula in A2 as
=getURL(a1). So I need this to be completely generic so that I can paste
it into hundreds of cells and always have it refer to the cell directly
to the left regardless as to which column or row it is in. Thanks

Paul

 

[Bernie Deitrick]

Paul,
For the cell directly to the left, use this in the cell, replacing the B1 with the actual cell
address
=OFFSET(B1,0,-1)

For the cell above:
=OFFSET(A2,-1,0)

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

if you put that formula in B1:
=geturl(a1)

You can copy and paste anywhere and it will refer to the cell to its immediate
left.

 

[Bernie Deitrick]

Dave and Paul,

That won't work if a new column B is inserted....

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

Yep, but it may be better (or not...).

If the user inserts a new column B, then maybe the formula should continue to
point at column A????

 

[bplumhoff]

Hello Paul,

Define the name LEFTVAL with the formula
=GET.CELL(5,INDIRECT("RC[-1]",))
for example. Then you can use =getURL(LEFTVAL) wherever you like.
It will give getURL() the value of the adjacent cell to the left, no
matter whether you insert columns later on.

See http://www.sulprobil.com/html/get_cell.html for more info...

HTH,
Bernd