Excel 2000- Autonumber & x reference

[Jules]

Hi,
Hope help is at hand. I am creating a pricelist(sheet 1) wit
explanitory notes(sheet 2) in a seperate worksheet which I want t
crossreference to each other.

I have used a simple formula adding 1 to the value of the previous cel
ie H14= H13+1 and named(bookmarked) each of these number list cells.
In sheet two I applied the name given to the relevant note.

The problem arises when a line is deleted from sheet 1 say H13, th
calc method I have used (H14=H13+1) no longer works, "=SUM(#REF!+1)"

further - I would like the note corresponding to the deleted line t
also be removed

Please tell me a better way of doing it I am sure there must be one!
Many thank

 

[Frank Kabel]

Hi
try the following:
in H1 enter the following formula
=IF(I1<>"",1,"")

in H2 enter the formula
=IF(I2<>"",MAX($H$1:OFFSET($H2,-1,0))+1,"")
copy this down

The deletion of the note is not that simple. This could be only
achieved with VBA (e.g. using an event procedure)

 

[Jules]

instead of using H14=H13+1

used =ROW()-3 -3 because the calc's start on row 4
and the problem goes away

still need to delete the note relating to it but think i can probabl
do with a macr