Is there a "self" reference for forumula's? 1

[NBartomeli]

I am trying to modify a worksheet for a woman in HR that lists all the employees and some information about them. Every time she adds or removes an employee the formula fields are off, circular references occur, etc.. since she just deletes or adds rows from the sheet.

since the starting row is always the same, I was wondering if I could do something like "=SUM(N13:SELF)" or something, so it will always SUM a range from N13 to wherever the cell with the formula is, whether it moves up or down any number of rows.

Thanks in advance

 

[Zathras]

Try this:
[tt]
=SUM(INDIRECT("N13:N"&ROW()-1))
[/tt]

 

[kphu]

you can name a range and force the person to only insert or delete above a particuler line.


Say you have 5 employees right now names in column A and salary in column B.

You want to sum the values in column B. Highlight B1 thru B6 and name the range as "test" or what ever you like.

For your formula type in =sum(test)

Now to keep your users from entering in a new person into a row that's not in your selected range. In cell A6 type in "You must insert or delete lines above this row." This way if the user deletes or inserts a row the range "test" will account for the new row.

This is the only solution I've come up with without getting into writing code.

Perhaps someone else may have an alternative.

In any event good luck.

Ken