Count the number of blank cells until next filled cell is found

[Bookender]

Okay, so. I have a sheet with data in all sorts of random cells. However, I only need the information from a few specific ones, so, when I import those columns, I get data that's all over the place.

1	Fish
2		Spoon
3
4		Drink
5
6	Tree
7	Shoe		Four
8			Eight
9
10		Night

<TBODY>
</TBODY>

I would like to get this data 'gathered' so that it takes up as little space as possible, while still keeping the different rows apart. And I want to do this with an INDEX command (no arrays, no VBA). I want to do this by having a helper column that does a simple check, and adds one to the number above itself if any of the cells in a row isn't blank; otherwise (and this is where I have trouble), I want it to add the number of blank cells until next non-blank is encountered. So the row with Fish would be 1, the row with Shoe would be 2, and then the row after that would say 4, as it counts the blank cell before Drink - and then the sequence would continue with 6, 7, 8, 10. In order. As demonstrated:

1

2

4

6

7

8

10

<TBODY>
</TBODY>

Anyone got any ideas for how to do that counting? (my apologies if this exact question has already been answered, but an intial search did not yield anything I could use)

 

[Bookender]

An additional thank you, Oldbrewer, for teaching me about OFFSET. I've been able to work it into some of my other codes, which, among other things, has cut the calculation time of one spreadsheet nearly in half. So, once again, thank you so much.

 

[oldbrewer]

you are welcome = when I was still at work I found offset/match and sumproduct to be extremely useful. Offset match can be used to chart data and by changing just one cell you can change the range charted just like that...