Using Functions to sequential number items base on other cells that may be blank.

[puforee]

I have a spreadsheet with a column that sequentially numbers (number column) itself based on the cell in another column (compare column). Not to hard. But if the compare column cell is blank I don't want to sequence the number column. This is doable also. Now, when I go to the next cell in the number column and the compare column cell has data I want to continue sequencing the number from where I left off.
Currently I am trying to use Functions to do this but.......

Example:

Number Column Compare Column
1 has data
2 has data
(blank)
(blank)
3 has data
(blank)
4 has data
etc.

Can anyone help with this?

 

[SkipVought]

Hi,

Unless there is more to your table, this is a big problem, ie table/list with empty rows. Technically the data below your empty row is NOT PART OF THE TABLE.

So why the empty rows?

 

[puforee]

Good question. So...more detail. We are building a spreadsheet to show questions about the data in the second column. Not all data in the second column will have a question, as shown blow. We want the quesions to be number sequentially only if there is a Question. The Data (column 2) is important to keep and it's order is important to maintain. Eventually all of this will go into a DB and we will be able to print a set of questions in the order of Column 1. So, I can't resort by column C because I would loose the integrity of column 2. Actually there are more columns that the sort could mess up...but trying to keep this simple.

QuestionNum DataCopiedFromElseware QuestionAboutData
1 Data Question
2 Data Question
Data (Blank)
Data (Blank)
Data (Blank)
3 Data Question

 

[SkipVought]

Well then you're shooting yourself in the foot with TWO data sets in one table:
1) questions and 2) something other than questions!

You shouldn't have DataCopiedFromElsewhere unless there's a question in your Question Table.

 

[Andrzejek]

It also may be beneficial to show your data like this (use PRE tag)

[pre]
QuestionNum DataCopiedFromElseware QuestionAboutData
1 Data Question
2 Data Question
Data (Blank)
Data (Blank)
Data (Blank)
3 Data Question
[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Deniall]

Skip & Andrzejek are spot-on in pointing out that your problem arises primarily from the way you have structured your data.

However, treating your question as an academic exercise, one way to achieve the visual effect you seem to want would be to use the formula
=IF(ISBLANK(B9),A8,A8+1)
in, for example, cell A9.[ ] (Obviously this cannot be used for the very first entry in column A.)[ ] Then use conditional formatting to render cell A9 in white text on a white background if (A9=A8).

 

[puforee]

Thanks guys..but I am using the spreadsheet as a template. And different users paste in different information into column J and then write questions about some or all of the informaion in column K. Column E is where the questions are numbered based on the information below.

I did get my problem solved with this =IF(ISBLANK($K2),"",COUNTA($K$2:$K2)) each row references it's own column K value.

The copied data resides in column J. The questions, if written are in column K. The above fomula is in Column E (Question Number). It works great.


Thanks for all your comments above.