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 |
=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. |
.. 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 |
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. |
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 ---- |
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 ---- |
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. |
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 |
All times are GMT +1. The time now is 02:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com