Home |
Search |
Today's Posts |
#1
|
|||
|
|||
A SIMPLER FORMULA TO GET A REFERENCE OF WHICH COLUMN IS POPULATED
Hi, need help on for a simpler formula is available if available. The
problem is I have a spreadsheet with many columns and I would like to know which column is populated, for the moment I'm using the following : =IF(I150,COLUMN(I15),"")&IF(J150,COLUMN(J15),"") &IF(K150,COLUMN(K15),"")&IF(M150,COLUMN(M15),"") &IF(N150,COLUMN(N15),"")&IF(O150,COLUMN(O15), "") The values can only be positive. A simpler version would be appreciated. Thanks |
#2
|
|||
|
|||
=MATCH(9.99999999999999E+307,I15:IV15)+COLUMN(I15)-1
Ravi wrote: Hi, need help on for a simpler formula is available if available. The problem is I have a spreadsheet with many columns and I would like to know which column is populated, for the moment I'm using the following : =IF(I150,COLUMN(I15),"")&IF(J150,COLUMN(J15),"") &IF(K150,COLUMN(K15),"")&IF(M150,COLUMN(M15),"") &IF(N150,COLUMN(N15),"")&IF(O150,COLUMN(O15), "") The values can only be positive. A simpler version would be appreciated. Thanks -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
#3
|
|||
|
|||
.. know which column is populated ..
If only a single cell within I15:O15 would be populated at any time, then this might suffice: =IF(ISNA(MATCH(TRUE,I15:O15<"",0)),"",MATCH(TRUE, I15:O15<"",0)+8) Formula to be array-entered, i.e. press CTRL+SHIFT+ENTER -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Ravi" wrote in message ... Hi, need help on for a simpler formula is available if available. The problem is I have a spreadsheet with many columns and I would like to know which column is populated, for the moment I'm using the following : =IF(I150,COLUMN(I15),"")&IF(J150,COLUMN(J15),"") &IF(K150,COLUMN(K15),"")& IF(M150,COLUMN(M15),"")&IF(N150,COLUMN(N15),"")& IF(O150,COLUMN(O15),"") The values can only be positive. A simpler version would be appreciated. Thanks |
#4
|
|||
|
|||
Thanks, That's great.
This formula works. Just 1 issue I'm getting which is it also picking up columns with Zeros' (this is a result of a formula) . I wanted to extract only the positive values. Is there another tweak to it that I need to make. "Aladin Akyurek" wrote: =MATCH(9.99999999999999E+307,I15:IV15)+COLUMN(I15)-1 Ravi wrote: Hi, need help on for a simpler formula is available if available. The problem is I have a spreadsheet with many columns and I would like to know which column is populated, for the moment I'm using the following : =IF(I150,COLUMN(I15),"")&IF(J150,COLUMN(J15),"") &IF(K150,COLUMN(K15),"")&IF(M150,COLUMN(M15),"") &IF(N150,COLUMN(N15),"")&IF(O150,COLUMN(O15), "") The values can only be positive. A simpler version would be appreciated. Thanks -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
#5
|
|||
|
|||
Perhaps another play to try in the interim ..
Assuming only a single cell within I15:O15 would evaluate to 0 at any time, try, array-entered: =IF(ISNA(MATCH(TRUE,I15:O150,0)),"",MATCH(TRUE,I1 5:O150,0)+8) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#6
|
|||
|
|||
Thanks guys. That was very useful and successful.
"Max" wrote: Perhaps another play to try in the interim .. Assuming only a single cell within I15:O15 would evaluate to 0 at any time, try, array-entered: =IF(ISNA(MATCH(TRUE,I15:O150,0)),"",MATCH(TRUE,I1 5:O150,0)+8) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#7
|
|||
|
|||
Glad to hear that, Ravi !
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Ravi" wrote in message ... Thanks guys. That was very useful and successful. |
#8
|
|||
|
|||
Hi Ravi, Try this array formula, =MAX(IF(ISNUMBER(I15:O15)*(I15:O15<0),COLUMN(I15: O15))) HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=387439 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell reference in a formula is called | Excel Discussion (Misc queries) | |||
Using a formula to create named range reference | Excel Worksheet Functions | |||
reference a repeating cell in a formula | Excel Worksheet Functions | |||
formula to return the value of a cell based on a looked up true reference | Excel Worksheet Functions | |||
Look Up and Cell Reference - Formula Help Needed | Excel Worksheet Functions |