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
2			A4, A7, A10, A13
3
4	0.622
5
6
7	0.609
8
9
10	0.556
11
12
13	0.585
14

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet50​

Array Formulas Cell Formula C2 {=TEXTJOIN(", ",1,SUBSTITUTE(ADDRESS(1,COLUMN(A4),4),1,"")&AGGREGATE(15,6,ROW(A4:A14)/(A4:A14<>""),ROW(INDIRECT("1:"&COUNTA(A4:A14)))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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
3	non-blanks	4
4		A4
5		A7
6		A10
7		A13
8
9

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet50​

Worksheet Formulas Cell Formula D3 =COUNTA($A$4:$A$14) D4 =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))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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.

0.622		A4 A7 A10 A13
0.609
0.556
0.585

<colgroup><col><col><col></colgroup><tbody>
</tbody>