finding addresses

D

donone

I have tried for several hours and failed.
I want, say, A1 to contain 1.
I want A2 to self generate its value as the cell above +1
etc. etc. but of course NOT using A1+1 because...

...if I delete one of the numbers in the sequence, I want that cell to
become blank and all the remaining cells below to regenerate their
numbers to remain sequential regardless of the blank or several
blanks.
One further limit is that if the renumbering hits a red cell (or
several) then it misses them and continues below.

I have tried Address(), row(), column() etc in various combinations to
no avail.

I would be pleased if someone could solve this for me.

Thanks
 
R

Ron Coderre

Try using a variation of this:

A2: =MAX($A$1:OFFSET(A2,-1,,,))+1

Copy that cell anywhere in column A. It will ignore blanks and text
when incrementing.

Does that help?
Ron
 
D

donone

Thanks for looking at this Ron

I have tried it. A1=1 then paste to A2 it gives 2, I then have to copy
and paste to all other cells and it generates the sequence. Part one OK
thanks.

The requirement (my para 2) to re-generate if one of the cells is
deleted does not happen. Nor does it skip cells containing a colour.
These two are the main aims.

Once the numbers are in, the above is required to happen automatically
as I delete a cell, they renumber below that point.

Regards
 
D

donone

Ron
If you are still around I offer my sincere apology. It does work as yo
say.

I think I pasted in the wrong place then corrected it and messed th
rest up for further tests.

Your solution is elegant.

May I ask one further question? It does jump over text, but is i
possible to jump a coloured cell?

Thank you
Donon
 
R

Ron Coderre

There are no built-in Excel functions that return cells' background
colors. You would need to create a custom function to account for
those. It can be done but why can't the cells be flagged in some
value-based way, instead of using color. Perhaps with a helper column
(like an X in an adjacent column)? That way you could remain within
the realm of Excel's native functions and functionality: Flag rows with
an X and conditionally format impacted cells so they have a fill color.

Regards,
Ron
 
D

donone

Thank you Ron
I believe that will indeed solve my problem and thank you for you
help. I guess I got fixed in my ways about colour in the cell.
Regards :
 
Top