Cell coordinates for next non-blank cell in a column

[TheDave]

Good grief, this shouldn't be as hard as it's been for me. I'm just not as well-versed with the INDEX/MATCH stuff, so I'm stuck with the syntax here.

Got a column with a bunch of dropdown menus, data validation already set up there and all that. I need cells in a separate column to give me the cell coordinates (CELL, address) for all the non-blank dropdown menus. I don't want the values, just the coordinates.

So for instance, B2:B81 has dropdown menus (all independent of each other). If I select values for, let's say, B12, B25, and B48, I want my formula in column K to return (in different cells - the below results would be in K2, K3, and K4 respectively)

$B$12
$B$25
$B$48

I'm pretty sure I just need the right combination of CELL and MATCH, with some FALSE,ISBLANK in there, but I'm stuck. Any ideas?

 

[DRSteele]

Well...I have one idea. let's try this. I have a DataValidation list that includes blanks. The dropdowns that refer to that list are in A4:A14. The formula reports (all in one cell, assuming you have Excel2106) all the addresses that are non-blank in the reference A4:A14.

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6"]A4, A7, A10, A13[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7030A0]#7030A0[/URL] , align: right"]0.622[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7030A0]#7030A0[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7030A0]#7030A0[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7030A0]#7030A0[/URL] , align: right"]0.609[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7030A0]#7030A0[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7030A0]#7030A0[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7030A0]#7030A0[/URL] , align: right"]0.556[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7030A0]#7030A0[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7030A0]#7030A0[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7030A0]#7030A0[/URL] , align: right"]0.585[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7030A0]#7030A0[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet50​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=TEXTJOIN(", ",1,SUBSTITUTE(ADDRESS(1,COLUMN(A4),4),1,"")&AGGREGATE(15,6,ROW(A4:A14)/(A4:A14<>""),ROW(INDIRECT("1:"&COUNTA(A4:A14)))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[TheDave]

DRSteele - thank you for your idea! I'm not sure I followed what the various numbers (by themselves) represented, so I'm not 100% sure how to modify the formula to fit my needs. I can switch the ranges around, no problem. But what about the 1s, 4, 15, and 6? Also, I'd love to get the cell references ultimately in cells by themselves. I suppose I can handle that with a different formula, delineating by comma as necessary.

 

[DRSteele]

You're welcome.

Those numerals are the arguments in their various Functions. The 15 inside AGGREGATE means 'use the small operation' and the 6 means 'ignore errors' (which I intentionally created to filter out the blank rows). The 1 inside ADDRESS means use Row 1 (which is as good as any) and the COLUMN part reports the column of cell A4. The 1 in SUBSTITUTE means replace the 1 that ADDRESS created with a blank, thereby reporting column "A". Combine that column "A" with the AGGREGATE thing and you get the non-blank addresses in the relevant range. TEXTJOIN ignores blanks(that's the 1 inside it) and joins all the relevant cells together with a comma.

If you don't want the answers all in one cell, you'll have to predict how many cells will be non-blank and then populate a section of the spreadsheet with a formula in each of those rows. I put this forumla (that uses expanding ranges) into cell D4 and copied it down far enough to capture what I predicted to be an adequate number of rows or farther(here it was four, as shown in D3).

	C	D
non-blanks

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] , align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]A4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]A7[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]A10[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]A13[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"][/TD]

</tbody>

Sheet50​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=COUNTA($A$4:$A$14)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=IF(ROWS($D$4:D4)>$D$3,"",SUBSTITUTE(ADDRESS(1,COLUMN(A4),4),1,"")&AGGREGATE(15,6,ROW($A$4:$A$14)/($A$4:$A$14<>""),ROWS($D$4:D4)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mike Szczesny]

Can you use something like this =concatenate(address({4,7,10,13},{1,1,1,},4,1(&" "). Before you hit enter, you need to highlight .....address(.......... all of the formula except the last ) in the formula. Hit F9. Remove both { and }. Hit enter.

[TABLE="width: 218"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.622[/TD]
[TD][/TD]
[TD]A4 A7 A10 A13 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.609[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.556[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.585[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]