Set a range in a formula based off the value of a cell

[criscocube]

Hi all,

Simple question, but I am confused... tried using versions of indirect and concetenate with no luck.

How do I set the below formula range to be based off the value of a cell?

=INDEX([I][B][U]valueofcell[/U][/B][/I]:$M$2,MATCH(TRUE,[I][B][U]valueofcell[/U][/B][/I]:M3>0,0))

The cell I am getting the value for e.g. has the value of "J3" (it comes from the formula:

=ADDRESS(A3,C3+6,4)

)

Thanks!

 

[Marcelo Branco]

What are the values in A3 and C3?

If A3 contains the row of interest maybe this
=INDEX(INDEX(M:M,A3):$M$2,MATCH(TRUE,INDEX(M:M,A3):$M$2>0,0))
Ctrl+Shift+Enter

M.

 

[criscocube]

Yes A3 just has the row number (3)

C3 is an artificial column number (offset by 6).

Cell D3 has the cell that I want to start the range with (J3).

Your addition seems close but something is off.... my brain hurts today haha. Thanks!

 

[criscocube]

i have a sample version of the spreadsheet:

https://ufile.io/jn1a4

 

[Marcelo Branco]

I don't understand what you are trying to do

Tell us the expected results (cells with ?)

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

1​

2​

years​

First positive year​

First positive cell​

First positive row number​

Goes negative again?​

1​

2​

3​

4​

5​

6​

7​

3​

3​

cash flow n 1​

4​

J3​

10​

?​

-10​

-8​

-5​

2​

3​

7​

40​

4​

4​

cash flow n 2​

3​

I4​

9​

?​

-6​

-6​

4​

6​

7​

-2​

-3​

M.

 

[criscocube]

the ? cells would do the same thing as the cells in column C, except they would start after the "first positive cell" (in column d)

 

[Marcelo Branco]

the ? cells would do the same thing as the cells in column C, except they would start after the "first positive cell" (in column d)

Confused...
The header says: "Goes negative again?"
Are you looking for the first negative after the first positive?
Please, be explicit: tell us the the expected results considering the data sample above.

M.

 

[criscocube]

sorry for not being explicit

yes that is exactly what i want... the first negative starting after the first positive (if any)

 

[Marcelo Branco]

No need of columns E and D

Try this in F3 copied down
=IFERROR(INDEX(INDEX(G3:M3,C3):M3,MATCH(TRUE,INDEX(G3:M3,C3):M3<0,0)),"Not Found")
Ctrl+Shift+Enter

M.

 

[criscocube]

far more clever, works perfect! thank you very much!