Count blank cells until a non-blank cell is reached

  • Thread starter Thread starter tim
  • Start date Start date
T

tim

Does anyone know how to count the number of blank cells
until a value is reached?

For example, I have dates as the column headings (one for
each day of the year), then people in the rows. Each date
the person buys something I enter a value in the
corresponding row for the date they purchased. At any
time, I wish to know how long it is since someone
purchased.

any ideas?

thanks
 
Hi Tim,

Here's one way,

Assuming that you have your dates on Row 1 as your column headings and
that the dates start in B1 onward, and assuming that each row represents
a person, and the data starts on Row 2, try the following:

1) Enter this formula on Row 2 at the end of your row (after the dates)
and copy it down as far as you need to:

=SUM(TODAY()-INDEX($B$1:$H$1,MATCH(9.99999999E+307,B2:H2)))

2) Adjust the range to include all of your dates.

Hope this helps!
 
I forgot to mention that after entering the formula and copying it down
your column to format the cells as "General".
 
Domenic

thanks for your help, but I get a "N/A" error message on
all rows except the date row. The data in each row is a
value rather than a date (see attached). Any other ideas?

Person 04/05/04 05/05/04 06/05/04
Jimmy 12.55 15.00

if the date I ran this report was the 10th May (we use
dd/mm/yy format), I would wish to know that it was 5 days
since Jimmy purchased anything.

thanks

Tim


-----Original Message-----
I forgot to mention that after entering the formula and copying it down
your column to format the cells as "General".
Click to expand...
 
Okay, I think I got it. :-)

In the first row where you have your dates, use a cell after your data
to house your date criteria. For this example, I'm using Cell I1.
Then, in the second row, in Cell I2, enter this formula and copy it down
as far as you need to:

=SUM(I$1-INDEX($B$1:$H$1,MATCH(9.99999999E+307,B2:H2)))

Don't forget to adjust the ranges ($B$1:$H$1 and B2:H2) to suit your
table.

Is this what you're looking for?
 
Domenic

thanks again, but still no luck

could you tell me what the 'sum', 'index' and 'match' are
trying to do, then I could look at the logic and maybe
amend the formulae?

many thanks

Tim
 
Here, the Index/Match functions basically looks for the last value
within the range B2:H2, and references the corresponding cell in the
first row where the dates are stored.

Let's recap, and make sure we're in the same wave length:

1) Dates are stored in the first row, starting at B1 through H1
2) Names are entered in Column A, starting at A2
3) Amounts for any purchases made are entered in the second row,
starting at B2 through H2
4) Each row after that continues, with the name of the person making the
purchase and any purchases he/she made
5) Cell I1 contains the date you run your report
6) Cell I2 contains the following formula, which you copy down the
column as far as you need to:
=SUM(I$1-INDEX($B$1:$H$1,MATCH(9.99999999E+307,B2:H2)))

If you're still having problems after this, let me know exactly what
sort of problem you're having or, if you prefer, I can email you the
sample I have.
 
Let A1:D1 house:

Person 04/05/04 05/05/04 06/05/04

Let B2:D2 house:

Jimmy 12.55 15.00

where D2 is empty.

=TODAY()-LOOKUP(9.99999999999999E+307,$B$2:$D$2,$B$1:$D$1)

Format the formula cell as General.

While shorter, this formula is equivalent to the one Domenic suggested.

tim said:
Domenic

thanks for your help, but I get a "N/A" error message on
all rows except the date row. The data in each row is a
value rather than a date (see attached). Any other ideas?

Person 04/05/04 05/05/04 06/05/04
Jimmy 12.55 15.00

if the date I ran this report was the 10th May (we use
dd/mm/yy format), I would wish to know that it was 5 days
since Jimmy purchased anything.

thanks

Tim
Click to expand...
 
"Let B2:D2 house:" should be: "Let A2:D2 house:"

Aladin Akyurek said:
Let A1:D1 house:

Person 04/05/04 05/05/04 06/05/04

Let B2:D2 house:

Jimmy 12.55 15.00

where D2 is empty.

=TODAY()-LOOKUP(9.99999999999999E+307,$B$2:$D$2,$B$1:$D$1)

Format the formula cell as General.

While shorter, this formula is equivalent to the one Domenic suggested.
Click to expand...
 
This might help. I'm new to posting message boards so forgive me if
screw this up.

You can name all of the rows you need checked then refer to that rang
in your code. Highlight the rows then go to name box and enter
name(daterange) and hit return.

Dim myrange as range
Set myrange = Worksheets("sheet1").Range("daterange")

For Each rw In myrange.Rows
If rw.Value = "" Then
'do something
Else
'do something else...maybe check the format
End If
Next rw

There is also a function that counts blanks aslo.
Application.WorksheetFunction.CountBlank(myrange)

Not sure if you were using vb of just excel functions but I am usin
this now to highlight blank fields in certain columns. Hope thi
helps
 
Back
Top