Ask Your Question
0

INDIRECT and arithmetic

asked 2019-08-31 23:27:46 +0100

stellr gravatar image

Indirect seems to work for group function ":" =SUM(INDIRECT("F4:" & "D" & L2)) When I try to use other functions it gives a #REF! error: =SUM(INDIRECT("F4+" & "D" & L2)) Is there a workaround?

edit retag flag offensive close merge delete

Comments

INDIRECT returns reference (i.e., it returns range of cells). What range do you expect from an expression like "F4+D6"?

Mike Kaganski gravatar imageMike Kaganski ( 2019-08-31 23:31:42 +0100 )edit

I see. Is there some other way to perform this intent, use the value in a cell as a row value in an arithmetic expression?

stellr gravatar imagestellr ( 2019-08-31 23:52:09 +0100 )edit

=F4+INDIRECT("D"&L2)

Mike Kaganski gravatar imageMike Kaganski ( 2019-09-01 00:04:23 +0100 )edit

Perfect, the light comes on.

stellr gravatar imagestellr ( 2019-09-01 00:08:11 +0100 )edit

Don't see a button to mark this answered, give credit. Am I missing it or is that not on me?

stellr gravatar imagestellr ( 2019-09-01 00:15:39 +0100 )edit

If this answer helped you, please accept it by clicking the check mark ✔ to the left and, karma permitting, upvote it. That will help other people with the same question.

robleyd gravatar imagerobleyd ( 2019-09-01 03:56:06 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-09-01 00:26:21 +0100

INDIRECT returns a reference to a range of cells, it is not a way to write arbitrary formulas in textual form.

To add a fixed cell to a cell with address taken indirectly, use

=F4+INDIRECT("D"&L2)
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-08-31 23:27:46 +0100

Seen: 46 times

Last updated: Sep 01