i am using excel2007 and i'm making a database system here is want...
this is my first sheet, the input sheet.
then this is my masterlist sheet
my problem is what will be the formula(maybe a count formula, at cell C2 of the input sheet) that counts the code like in the masterlist, if i input another entry for HR Department it will be HR-003 and of I input an entry for Finance it will be FN-001. I want to be auto generated of number, if i input a department then it will generate the next number that based on the number that shown in the masterlist.
Please Help me!!!
Thank you in advance....
this is my first sheet, the input sheet.
| Excel Workbook | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | ||||||||||
| 2 | Code No. | |||||||||
| 3 | DEPARTMENTS | |||||||||
| 4 | Department | HR | ||||||||
| 5 | SL for Sales | |||||||||
| 6 | Project Title | MK for Marketing | ||||||||
| 7 | FN for Finance | |||||||||
| 8 | Created by | OP for Operations | ||||||||
| 9 | ||||||||||
| 10 | Date Created | |||||||||
| 11 | Month | Day | Year | |||||||
| 12 | ||||||||||
| 13 | ENCODE | |||||||||
| 14 | ||||||||||
| 15 | ||||||||||
Input Sheet | ||||||||||
then this is my masterlist sheet
| Excel Workbook | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Date Created | ||||||||
| 2 | Code No. | Department | Project Title | Created by | Month | Date | Year | ||
| 3 | HR-001 | HR | HR ONE | CREATOR 1 | JUNE | 4 | 2010 | ||
| 4 | HR-002 | HR | HR TWO | CREATOR 2 | JULY | 4 | 2010 | ||
| 5 | SL-001 | SL | SL ONE | CREATOR 3 | DECEMBER | 5 | 2010 | ||
| 6 | OP-001 | OP | OP ONE | CREATOR 4 | SEPTEMBER | 6 | 2010 | ||
| 7 | SL-002 | SL | SL TWO | CREATOR 5 | AUGUST | 19 | 2010 | ||
| 8 | MK-001 | MK | MK ONE | CREATOR 6 | NOVEMBER | 20 | 2010 | ||
| 9 | |||||||||
| 10 | |||||||||
| 11 | |||||||||
| 12 | |||||||||
Sheet2 | |||||||||
my problem is what will be the formula(maybe a count formula, at cell C2 of the input sheet) that counts the code like in the masterlist, if i input another entry for HR Department it will be HR-003 and of I input an entry for Finance it will be FN-001. I want to be auto generated of number, if i input a department then it will generate the next number that based on the number that shown in the masterlist.
Please Help me!!!
Thank you in advance....
