Ask Your Question
0

ADDRESS result isn't accepted as parameter

asked 2016-02-18 12:52:54 +0200

Lorenz25 gravatar image

updated 2016-03-08 22:01:35 +0200

Alex Kemp gravatar image

I want to get the value one row above the current cell, like this:

 =OFFSET(ADDRESS(ROW(),COLUMN(),4), -1, 0)

But it doesn't accept the reference. Error 504.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-02-18 17:03:21 +0200

w_whalley gravatar image

The function ADDRESS() returns a text representation of the cell address. That doesn't work for the OFFSET function, hence the 504 parameter list error. You could use the INDIRECT function to convert the text version of the address to a usable one, i.e.

=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0,1,1)

but why not simplify and use

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))
edit flag offensive delete link more

Comments

Thank you. Should've looked up the ADDRESS function, instead of just copy/paste.

Lorenz25 gravatar imageLorenz25 ( 2016-02-19 18:48:21 +0200 )edit
0

answered 2016-02-18 20:25:32 +0200

karolus gravatar image

Hallo

I want to get the value one row above the current cell, like this:

Why the hell not simply =<the cell name one-above>, or if you need some oversophistication:

=OFFSET(<this-cell>; -1; 0)

for Example the Fomula for Cell D7 →→

=OFFSET(D7;-1;0)
edit flag offensive delete link more

Comments

After I've given it some thought, I think this would work too. Had some doubt about the reference always being the cell of the formula, if copied elsewhere. So thank you as well.

Lorenz25 gravatar imageLorenz25 ( 2016-02-19 18:53:50 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-02-18 12:52:54 +0200

Seen: 130 times

Last updated: Feb 18 '16