Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am working up a basic project tracking spreadsheet in Excel and would like to have two sheets - one which shows my active projects and one which shows my completed/cancelled/archived projects. I would like to assign each project a unique number so I know I would want to have data validation on my active sheet so that when I create a new project, and enter a project number, Excel reviews a specified range on the other sheet to see if I've used that number before. I am sure this is possible but am not sure of the logic I would need to use. Does anyone have any advice?


A branch off of this, does anyone have a suggestion as to how I could maintain these two lists of active / archived projects and maintain an incremental project numbering system in Excel?

share|improve this question

I would consider using Access instead of Excel. The behavior you want is built in to its basic functionality.

share|improve this answer
Funny...I honestly didn't consider Access but that is probably because I have been brainwashed to avoid it whenever possible. I think that I will create my project registry in Access. – Gryphoenix Sep 6 '13 at 19:22

MS Access really is the better option but it can be done in Excel.

You could keep all the records in the same sheet regardless of their status and then just use a filter to show only the status you want to look at. Doing so, you could quickly bounce between the different statuses.

When adding a new record you could take the filter off and sort by project unique number to see where you left off.

Filtering and sorting is only a few mouse clicks.

share|improve this answer

You could use conditional formatting to flag a cell if the project number (assuming its numeric only) is lower than the maximum number used in your completed/archived projects. It's a little kludgy when using multiple sheets, since you can't reference a different sheet in a conditional format.

Something like "Format only cells that contain:" Cell Value - less than or equal to - $BB$1

$BB$1 being the max of previously used project numbers on the different sheet (=MAX('Sheet1'!A1:A10))

share|improve this answer

You must log in to answer this question.

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