Ask Your Question

create a row number based upon return of a function

asked 2019-04-02 08:32:42 +0200

kngharv gravatar image

updated 2020-08-07 02:41:27 +0200

Alex Kemp gravatar image

how do I reference a row number based upon some value of a function?

My data is in Column B. But the actual row number depend on some other condition.

ideally, I want something like this in my formula

=T("the actual value is "&sheet1.B(rowNumber(output of function)))

if the output of a fuction is "23", then, essentially I will see

=T("the actual value is "&sheet1.B23)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-04-02 12:32:30 +0200

m.a.riosv gravatar image

updated 2019-04-03 13:39:18 +0200

With INDIRECT() you can get it. Also it can be done with INDEX() and OFFSET().

edited 20190403

With INDIRECT you need to compose text of the address:
T("the actual value is "&INDIRECT("sheet1.B"&(rowNumber(2+3)))

with INDEX() the position on the range T("the actual value is "&INDEX(sheet1.B1:B500;rowNumber(2+3)))

with OFFSET() is a displacement from the beginning of the range T("the actual value is "&OFFSET(sheet1.B1;rowNumber(2+3)))

edit flag offensive delete link more


much appreciate your answer. I actually was aware of three functions. except that I am having problem understand how to use them.

if you happened to have example on how to use it, please spoon feed me.

using my example, how to use indrect() / index() / offset(), assuming "output of my fuction" is simply "2+3"?

=T("the actual value is "&sheet1.B(rowNumber(2+3)))
kngharv gravatar imagekngharv ( 2019-04-03 00:11:49 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-02 08:32:42 +0200

Seen: 69 times

Last updated: Apr 03 '19