So I have a document that allows a user to select a site/department from a dropdown list. Each site/Department has a unique extension prefix e.g. 10, 11, 12, 13, etc. When a user selects the desired Site/Departments, I get the extension prefix using VLOOKUP depending on the users selection. What I now need to do is append 2 more digits to the extension prefix to create a 4 digit extension. So the output would be something like 1001, or 1002, but each extension needs to be unique. So I could generate a random number between 00-99, but how do I make sure that the generated number is not equal to any other number generated for that site? Different sites/departments can have the same generated number, because they'll have a different prefix.

Any ideas on how to solve this? I find myself in a pickle.

  • Do you have in one sheet multiple Dropdowns (with same department list)?
    – Varon
    Mar 1, 2016 at 19:47
  • The department dropdowns are all on the same page. They get their options from a different page though. Mar 1, 2016 at 19:49

1 Answer 1


The trick is to count up only until the current row with COUNTIF. So the Range is from the first row to the current row with the value of the current row.


Foo    10            Lorem         12      1        01    1201
Bar    11            Foo           10      1        01    1001
Lorem  12            Dolor         13      1        01    1301
Dolor  13            Foo           10      2        02    1002
                     Foo           10      3        03    1003
                     Bar           11      1        01    1101

All Columns Formula:

VLOOKUP:     =VLOOKUP(D2-D7;$A$2:$B$5;2;0)      // D2 in the first row up to D7 in the last
COUNTIF:     =COUNTIF($E$2:E2-E7;E2-E7)
TEXT:        =TEXT(F2-F7;"00")

For example is the first COUNTIF-Entry: =COUNTIF($E$2:E2;E2), the second one is =COUNTIF($E$2:E3:E3)

DepNand DepId is the constant "database" with all the departments. (I guess these columns should be hidden)

The DepDropdown is the Column with all departments each cell.

Of curse you can chain these 4 formulas and put them together in one column.


You must log in to answer this question.

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