Formula question

[edorminey]

Hi, I'm still learning Excel and need help with a formula.

I have a column T
I have positive numbers scattered out in 5 cells in the range T2:T100
(I have a formula in range T2:T100 that returns "" if the result is = 0 so most of the cells are blank except for the hidden formula)

I need to find the cells that are > 0 in range T2:T100 and put them in T105:T110 and also give me the values in column S in S105:S110.

Hope this is clear and thanks for your help.

 

[edorminey]

I did find one more item that wasn't answered. Sorry.

In the column $T$2:$T$100, I have several cells that have #N/A in them.

I need to be able to find which ones have #N/A and return the cell that is in the R column and the S column

Any way to do this?

 

[Aladin Akyurek]

I did find one more item that wasn't answered. Sorry.

In the column $T$2:$T$100, I have several cells that have #N/A in them.

I need to be able to find which ones have #N/A and return the cell that is in the R column and the S column

Any way to do this?

Let's say that we have #N/A in T3, while all other values are legal. What do you want to happen with them, including #N/A in T3?

 

[edorminey]

I just need to know what value is in cells R3 if T3 = #N/A and then go all the way down that column T2:T100 looking for any more #N/A

 

[Aladin Akyurek]

I just need to know what value is in cells R3 if T3 = #N/A and then go all the way down that column T2:T100 looking for any more #N/A

Suppose we have:

fad	300	#N/A
kad	500	3
jad
nad	800	5
gad	1200	12
vad	900	8

<tbody>
</tbody>

in R3:T9 and nothing else, what do you want to see in the ranges that start with R105, S105, and T105 downwards?

 

[edorminey]

In R105 I would like to see fad
In S105 I would like to see 300

And since none of the others have #N/A, I would not need anything else in that particular range.

 

[Aladin Akyurek]

In R105 I would like to see fad
In S105 I would like to see 300

And since none of the others have #N/A, I would not need anything else in that particular range.

In T105? Just #N/A?

 

[edorminey]

In T105? Just #N/A?

I'm not making myself clear. Sorry

I want to find any instance of #N/A in the range T2:T100

and then display what is in the R column and what is in the S column.

For instance, R2: T5

ran 3 #N/A
fan 4 #N/A
tal 1 14
sal 8 12

I want to find #N/A in row 2 and return "ran" in R2 and "3" in S2
then I want it to look for the next instance and find #N/A in row3 and put "fan" in R3 and "4" in #N/A

Then, keep going all the way to row 100 doing that.

Aren't you up mighty late?

 

[Aladin Akyurek]

I'm not making myself clear. Sorry

I want to find any instance of #N/A in the range T2:T100

and then display what is in the R column and what is in the S column.

For instance, R2: T5

ran 3 #N/A
fan 4 #N/A
tal 1 14
sal 8 12

I want to find #N/A in row 2 and return "ran" in R2 and "3" in S2
then I want it to look for the next instance and find #N/A in row3 and put "fan" in R3 and "4" in #N/A

Then, keep going all the way to row 100 doing that.

Aren't you up mighty late?

If we have the range R2:T100 and we have just the following in that range:

fad	300	#N/A
kad	500	3
jad
nad	800	5
gad	1200	12
vad	900	8

<tbody>
</tbody>

which occupy the area R3:T4 and R6:T9. The rest of R2:T100 is totally empty.

What do you want to see in the output area that starts with R105:T105 downwards?

 

[edorminey]

If we have the range R2:T100 and we have just the following in that range:

fad	300	#N/A
kad	500	3
jad
nad	800	5
gad	1200	12
vad	900	8

<tbody>
</tbody>

which occupy the area R3:T4 and R6:T9. The rest of R2:T100 is totally empty.

What do you want to see in the output area that starts with R105:T105 downwards?

In this example, I would want #N/A found in T3 and then display "fad" in another cell which could be anywhere, but lets say put "fad" in P3
Since #N/A doesn't show up anywhere else, nothing else would be displayed.

If #N/A was where the "8" is in cell T9, I would want "vad" put in P9

 

[Aladin Akyurek]

In this example, I would want #N/A found in T3 and then display "fad" in another cell which could be anywhere, but lets say put "fad" in P3
Since #N/A doesn't show up anywhere else, nothing else would be displayed.

If #N/A was where the "8" is in cell T9, I would want "vad" put in P9

You must be aware of the fact the output you now request is different from the ones you specified earlier...

P2, just enter and copy down:

=IF(ISNA($T2),$R2,"")

With this you get R-values that correspond to the #N/A's in the T-range.