Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is it possible to do something like this in Excel? Like a list of active rows with an ordinal number in them.

There is a column B. It needs to show an ordinal number which represents the active row in the worksheet. Look at the image.

If the row is inactive, then column B will not have an ordinal number. Now, when I delete some ordinal number from the column B, the ordinal order has to update accordingly. Let's say I remove B7 cell text, how can I make column B updates its values. Look at the image.

Is it possible to do it in a simple way, without having to write a lot of script code?

PS. I am not an excel expert so it would be great if there is a simple solution via Excel's embedded formulas. :) PPS. this function is similar to Word's numbered list. If I delete any list item, subsequent elements will update its values accordingly.

EDIT

Column A lists all tasks from 1 to X. Column B lists active tasks. They are ordered by its status (active inactive). When I finish a certain task (example the 7th task), I just delete its value in column B and then manually fix all subsequent numbers. This way I know how many active tasks I have and which ones are active.

Please tell if it is still unclear and which part needs more explanation!!!

PS. Tasks are described in columns C,D and E, but that part was not relevant here.

share|improve this question
    
Does the value in column B depend on something else, or just manual input? –  Terry May 2 '12 at 7:39
    
@djerry Manual input. When I decide that the row is no longer active, I just delete the value and I want next elements to update accordingly. –  JoeM May 2 '12 at 10:29
    
@djerry Added better explanation –  JoeM May 2 '12 at 10:35
    
Thanks for the edit. Maybe you are approaching this problem from the wrong side. Could you also say what you are trying to achieve with this? I see you want to know how many active tasks you have, if this is the only thing, you should appoach this differently and I'll come up with some. –  Terry May 2 '12 at 11:17
    
@djerry I simply need to know the max number of tasks and I want to sort tasks by cardinal numbers. Once a task becomes inactive, I want to easily remove it from the list while list updated automatically. –  JoeM May 2 '12 at 17:24

3 Answers 3

up vote 2 down vote accepted

I think it would be easier to manage if you had an extra column where you could simply mark whether a row is active, say with an "x". Then you could have the ordinal rank of each active row calculated automatically in the next column.

For instance, if you make Column B hold "x"s to denote the row is active, in Column C you can enter the following (in C2):

=IF(B2="x",MAX(C$1:C1)+1,"")

Then fill this down the column.

screen capture

share|improve this answer
    
Extra column...thanks. Is is solvable without extra column? –  JoeM May 2 '12 at 17:25
1  
You're asking for a solution that allows the user to overwrite the values of cells that have formulas while retaining the formulas. That can't be done with worksheet formulas alone unless you adapt what you want like I suggested above. You could use some kind of VBA Worksheet_Change event, but that seems to me to be overkill. Even once it's implemented, I think the VBA solution is less intuitive and user-friendly than the extra column because it still requires the user to input ordinal data (which is prone to error). –  Excellll May 2 '12 at 20:11

Based on suggestions, I found the right and easy way to do this. Just using max function.

The formula is =MAX($B$X:BY)+1

For example, in the cell B2 I put formula =MAX($B$1:B1)+1 and then copy it to all other cells in the B column and voila - I got what I need. I can delete any cell value and all subsequent will update immediately.

Thanks guys!

share|improve this answer

Without knowing how the input is getting there or what it is being used for...

If you entered '1' into cell B1 and copy the following formula into cell B2:

=IF(B1 = "", OFFSET(B1,-1,0)+1,B1+1)

it will allow you to maintain a list and delete a single range. If you wanted to be able to delete 2 cells you would have to add another if block to check each previous cell.

![Offset allows you to delete a cells contents and 'jump up' one in the list if the above cell is empty][1]

Using a looping macro would give greater scalability.

More details about what your trying to achieve would help greatly

Have image to explain but cant post until I get more reputation :S

share|improve this answer
    
will update the question asap –  JoeM May 2 '12 at 10:31
    
post the screenshot in a comment and ping me with @slhck, I'll embed it –  slhck May 2 '12 at 11:04
    
@slhck Sorry, I did not understand what do you say? –  JoeM May 2 '12 at 11:06
    
@joeeoj I meant BiGXERO, he says that he has an image to post. –  slhck May 2 '12 at 11:11
    
@slhck: thanks but Excelll has a better answer anyways –  BiGXERO May 3 '12 at 23:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.