0

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.

5
  • Does the value in column B depend on something else, or just manual input?
    – Terry
    May 2, 2012 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, 2012 at 10:29
  • @djerry Added better explanation
    – JoeM
    May 2, 2012 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, 2012 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, 2012 at 17:24

3 Answers 3

2

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

2
  • Extra column...thanks. Is is solvable without extra column?
    – JoeM
    May 2, 2012 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, 2012 at 20:11
1

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!

0

Without knowing how the input is getting there or what it is being used for, you can try the following:

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.

Note: Offset allows you to delete a cells contents and 'jump up' one in the list if the above cell is empty

Using a looping macro would give greater scalability.

More details about what you're trying to achieve would help greatly.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .