finding addresses
 

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


--
donone
------------------------------------------------------------------------
donone's Profile: http://www.excelforum.com/member.php...o&userid=27237
View this thread: http://www.excelforum.com/showthread...hreadid=467475

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=467475

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


--
donone
------------------------------------------------------------------------
donone's Profile: http://www.excelforum.com/member.php...o&userid=27237
View this thread: http://www.excelforum.com/showthread...hreadid=467475

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

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

Your solution is elegant.

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

Thank you
Donone


--
donone
------------------------------------------------------------------------
donone's Profile: http://www.excelforum.com/member.php...o&userid=27237
View this thread: http://www.excelforum.com/showthread...hreadid=467475

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=467475

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


--
donone
------------------------------------------------------------------------
donone's Profile: http://www.excelforum.com/member.php...o&userid=27237
View this thread: http://www.excelforum.com/showthread...hreadid=467475