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 ?)


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][td="bgcolor: #DCE6F1"]

L​

[/td][td="bgcolor: #DCE6F1"]

M​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td]

years​

[/td][td]

First positive year​

[/td][td]

First positive cell​

[/td][td]

First positive row number​

[/td][td]

Goes negative again?​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td]

7​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

3​

[/td][td]

cash flow n 1​

[/td][td]

4​

[/td][td]

J3​

[/td][td]

10​

[/td][td]

?​

[/td][td]

-10​

[/td][td]

-8​

[/td][td]

-5​

[/td][td]

2​

[/td][td]

3​

[/td][td]

7​

[/td][td]

40​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

4​

[/td][td]

cash flow n 2​

[/td][td]

3​

[/td][td]

I4​

[/td][td]

9​

[/td][td]

?​

[/td][td]

-6​

[/td][td]

-6​

[/td][td]

4​

[/td][td]

6​

[/td][td]

7​

[/td][td]

-2​

[/td][td]

-3​

[/td][/tr]
[/table]

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!