How to find the first blank cell

[leahdon]

Hi all,

I've got a row representing months from May 2011 - May 2015. If the cell has a 1 in it, it means the person is working. If the cell has a blank, it means their role has finished.

Can someone help me create a formula that will return the cell reference (or better the column heading) of the first cell with a blank please?

eg
May11 June11 July11 Aug11 Sept11 End date
Person A 1 1 1 July11
Person B 1 May11
Person C 1 1 1 1 Aug11

Many thanks,

Donna

 

[leahdon]

T. Valko is correct, we're trying to get the s/s to report the first blank cell in a range. There are no filled cells after the first empty cell.

 

[Marcelo Branco]

Biff,

I'm getting #VALUE! to the formula
=INDEX(AD6:BZ6,MATCH(TRUE,AD27:BZ27="",0))

I've checked the formula build, and it does recognise the first 'ref' as an array.

Leadhon,

Please, try my formula

M.

 

[Marcelo Branco]

T. Valko is correct, we're trying to get the s/s to report the first blank cell in a range. There are no filled cells after the first empty cell.

If so what mean July11, May11 and August11 in your original post?

Are there the expected results or not?

M.

 

[sahild05]

No that doesn't help. I think the formatting before threw things off, so here's a second attempt. What I would expect to see in my example below (in the Not working cell) would be Aug 11 (or the cell ref) for person A, Jun 11 for person B and Sep 11 for person C.


<table style="WIDTH: 344pt; BORDER-COLLAPSE: collapse" border="0" cellpadding="0" cellspacing="0" width="459"><colgroup><col style="WIDTH: 48pt" width="64"><col style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width="75"><col style="WIDTH: 48pt" span="5" width="64"></colgroup><tbody><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height="17" width="64">name</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 56pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width="75">Not working</td><td class="xl63" style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right" width="64">May-11</td><td class="xl63" style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right" width="64">Jun-11</td><td class="xl63" style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right" width="64">Jul-11</td><td class="xl63" style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right" width="64">Aug-11</td><td class="xl63" style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right" width="64">Sep-11</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height="17">A</td><td class="xl63" style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">
</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">1</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">1</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">1</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">
</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">
</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height="17">b</td><td class="xl63" style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">
</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">1</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">
</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">
</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">
</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">
</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height="17">c</td><td class="xl63" style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">
</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">1</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">1</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">1</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align="right">1</td><td style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">
</td></tr></tbody></table>

Thanks,

Donna

how about

=OFFSET($B$1,0,SUM(C2:G2))

 

[leahdon]

Biff,

I'm getting #VALUE! to the formula
=INDEX(AD6:BZ6,MATCH(TRUE,AD27:BZ27="",0))

I've checked the formula build, and it does recognise the first 'ref' as an array.

Oops, sorry Biff, I've just noticed that I made a change to your formula hence the error. However, having now put the correct formula in the cell =INDEX($AD$6:$BZ$6,MATCH(2,AD31:BZ31)), and then converted the answer into the correct format, i'm finding that it gives me the date of the final cell in the range, not the first blank cell.

Donna

 

[T. Valko]

Biff,

I'm getting #VALUE! to the formula
=INDEX(AD6:BZ6,MATCH(TRUE,AD27:BZ27="",0))

I've checked the formula build, and it does recognise the first 'ref' as an array.

Hmmm...

Not sure why you'd get a #VALUE! error. Are there any #VALUE! errors already in the referenced range?

Here's a screencap that shows the formula does return the correct result:

 

[leahdon]

how about

=OFFSET($B$1,0,SUM(C2:G2))

That doesn't tell me which cell is the first blank cell in the range. I'm searching 300 rows of 40+ columns and each row has a different end date (represented by a 0 or blank).

 

[leahdon]

Hmmm...

Not sure why you'd get a #VALUE! error. Are there any #VALUE! errors already in the referenced range?

Here's a screencap that shows the formula does return the correct result:

Not sure what's going on, but using your formula, I've got to one that works for me.

=INDEX($AD$6:$BZ$6,MATCH(0,AD27:BZ27,0))

Wouldn't have got there without you, so thanks for your help.

Donna

 

[T. Valko]

Oops, sorry Biff, I've just noticed that I made a change to your formula hence the error. However, having now put the correct formula in the cell =INDEX($AD$6:$BZ$6,MATCH(2,AD31:BZ31)), and then converted the answer into the correct format, i'm finding that it gives me the date of the final cell in the range, not the first blank cell.

Donna

Ok, we're making progress!

Change that formula to:

=INDEX($AD$6:$BZ$6,MATCH(TRUE,AD31:BZ31="",0))

Make sure you array enter**.

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[sahild05]

That doesn't tell me which cell is the first blank cell in the range. I'm searching 300 rows of 40+ columns and each row has a different end date (represented by a 0 or blank).

all this does is add up the 1's. If they all start at the beginning, and are all 1's, and are contiguous, it should work just fine. Are you having specific issues when you try it?