Number Sequence problem

[jimmyglass]

Hi,
I have a problem which I cant find the answer too looking through the old posts.

The data I am dealing with is for bills of materials for various products;
along the lines of - [Part number; Sequence No; Operation: Material; Qty] in separate columns.

I have created a macro which deletes rows from the structure if the Qty column is equal to zero.

My problem is that after the rows that arent needed have been deleted, the sequence column is no longer correct as numbers in the sequence are missing. I need a way of them autocorrecting.

To complicate things there are a number of structures on one worksheet all following on from each other, and they are not always the same length, with each structure normally numbering between 5 and 25 rows.

The first row of each structure always contain 1 in the sequence column and is never deleted, and the last row in a structure always contains "Bookin" or "Stockin" in the operation column, so these could be used to define a range where the numbers in between have to be sequential.

I hope that makes sense!

I've tried using various formulas in the sequence column such as adding 1 to the cell above but this just results in #REF in the row below a deleted row.

Any help would be greatly appreciated!!

Jimmy

Forgot to mention I'm using Excel 97

 

[GlennUK]

How about this for the sequence column:

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1

 

[jimmyglass]

Yeah that works! But unfortunately it merges two columns together? and so overwrites the data in column D.

Thanks for your help

 

[GlennUK]

You said:

I've tried using various formulas in the sequence column such as adding 1 to the cell above but this just results in #REF in the row below a deleted row.

... so where did you try my formula? I thought you'd be putting it in the sequence column.

So what do you mean by "it merges two columns together"?

 

[jimmyglass]

I apologise, I've got it working now, I'm not sure what I did the first time!

Thanks for that, was a lot simpler than I thought it would be!

Jimmy

 

[GlennUK]

Great! I'm glad that's working now.