Countdown cells until an event in adjacent column

zeke333

New Member
Joined
May 22, 2019
Messages
3
How do I countdown the number of cells until an event in an adjacent column? I only want to look 7 days ahead.

DATEIRRIGATE (GALLONS)DAYS UNTIL IRRIGATE
1/1/2010--4
1/2/2010--3
1/3/2010--2
1/4/2010--1
1/5/20105000
1/6/2010--3
1/7/2010--2
1/8/2010--1
1/9/20105200
1/10/2010--3
1/11/2010--2
1/12/2010--1
1/13/20102100
cont...

<tbody>
</tbody>
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,752
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
Joined
May 22, 2019
Messages
3
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
Joined
Apr 25, 2006
Messages
19,752
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:

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top