# Countdown cells until an event in adjacent column

#### zeke333

##### New Member
How do I countdown the number of cells until an event in an adjacent column? I only want to look 7 days ahead.

 DATE IRRIGATE (GALLONS) DAYS UNTIL IRRIGATE 1/1/2010 -- 4 1/2/2010 -- 3 1/3/2010 -- 2 1/4/2010 -- 1 1/5/2010 500 0 1/6/2010 -- 3 1/7/2010 -- 2 1/8/2010 -- 1 1/9/2010 520 0 1/10/2010 -- 3 1/11/2010 -- 2 1/12/2010 -- 1 1/13/2010 210 0 cont...

<tbody>
</tbody>

#### pgc01

##### MrExcel MVP
Hi
Welcome to the board

What are the "--" that appear in your table

- are they the text value "--"
or
- are they for ex. the number zero that you hid with a number format?

#### zeke333

##### New Member
Treat them as zeros, a zero hidden as "--", or a text value, whichever way is easier for you to present a solution.

They are the result of an equation that either specifies an irrigation volume or not. I can make them show up as 0 if it makes a difference in your solution.

Hi
Welcome to the board

What are the "--" that appear in your table

- are they the text value "--"
or
- are they for ex. the number zero that you hid with a number format?

#### pgc01

##### MrExcel MVP
If the "--" is a real text value then you just have to look for the next number value, like in C2:

=MATCH(TRUE,INDEX(ISNUMBER(B2:\$B\$1000),),0)-1

Copy the formula down

if, however, the "--" is just a number format and the cell value is the number value zero, then look for the next number value bigger than zero, like in C2:

=MATCH(TRUE,INDEX(B2:\$B\$1000>0,),0)-1

Copy the formula down

Remark:
Amend the B2:\$B\$1000 range if 1000 rows is not convenient to your case.

Last edited:

#### zeke333

##### New Member
Excellent, Thank you!

You're welcome.

1,082,335
Messages
5,364,696
Members
400,811
Latest member
MSBINinja

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...