Document numbering system based on 6 document categories

anderson7777

New Member
Joined
Sep 2, 2014
Messages
5
Hello,


I am working on this excel based document numbering system to replace an ancient Lotus Notes database that has given us some issues. We have multiple types of documents and all I am trying to do is to automate the numbering system for each of the document types. One of these types is what we call high level procedures which also includes policies. This type of document covers 6 categories. The format we have been using is the following: Policy abc UK HIG 1.01. The UK HIG identifies that it is a high level procedure, the 1. identifies the category and the last two digits represents the actual numbering system for this type of documents (different for each category).


The problem is that: for the first category the numbering has to start at 18, for the second category the numbering has to start at 08, the third and the fifth category has to start at 31, the fourth category has to start at 71 and the sixth category has to start at 01. I have already hardcoded the documents that preceded these numbers.


For the category number I have used a separate column and, based on a dropdown list, I have managed to figure that part out (as explained below). The problem is the last two digits. Is there any IF statement that could add 1 to the previous entry of the same category? Or would it be easier to just create 6 separate sheets for each category?


The cells arrangement is as follows:
B13 - document owner
C13 - Document title
D13 - Date of issue
E13 - dropdown list for the 6 categories covered by this type of document
F13 - if B13,C13,D13>0, then this cell just writes UK HIG
G13 - if B13,C13,D13>0 and depending on what category is selected in E13, this cell has the number afferent to the category (1 through to 6)
H13 - this is where my problem resides; I have tried and entered the values next to the main table and just referenced the cell depending on what was selected in E13 but it is not consistent in the sense that, depending on how many documents of one category are entered, it will jump numbers for the rest of categories that follow. As an idea of how far I got to this extent, the following IF statement is where I am at:


Code:
=IF(AND(B13>0,C13>0,D13>0,E13=$L$12),O13, IF(AND(B13>0,C13>0,D13>0,E13=$L$13), P13, IF(AND(B13>0,C13>0,D13>0,E13=$L$14), Q13, IF(AND(B13>0,C13>0,D13>0,E13=$L$15), R13, IF(AND(B13>0,C13>0,D13>0,E13=$L$16), S13, IF(AND(B13>0,C13>0,D13>0,E13=$L$17), T13, ""))))))


where column L has the 6 categories that go in the dropdown list in E13; columns O - T is where I have entered the values for each category taking into consideration where numbers have to start (see second paragraph).


As much as I know that VBA holds the answer, I am too much of a beginner to use it. I have started learning but I do not feel comfortable with using VBA since I know that this system is to be used by about 50 people.


I would be forever grateful if someone wise has a solution to my problem.


many thanks,
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
you could shorten that to

=IF(OR(B13="",C13="",D13=""),"",IF(E13=$L$12,O13,IF(E13=$L$13,P13,IF(E13=$L$14,Q13,IF(E13=$L$15,R13,IF(E13=$L$16,S13,IF(E13=$L$17,T13,"")))))))
 
Upvote 0
Does this help you (formula in H2 copied down)?


Excel 2010
BCDEFGHIJK
1OwnerTitleDateCatCatCodeNoSuffixStart
2Owner1Title105/09/201411UK HIG1.18118
3Owner2Title205/09/20142UK HIG28
4Owner3Title305/09/201433UK HIG3.31331
5Owner4Title405/09/201444UK HIG4.71471
6Owner5Title505/09/201455UK HIG5.31531
7Owner6Title605/09/201466UK HIG6.0161
8Owner7Title705/09/201411UK HIG1.19
9Owner8Title805/09/201422UK HIG2.08
10Owner9Title905/09/201433UK HIG3.32
11Owner10Title1005/09/201444UK HIG4.72
12Owner11Title1105/09/201455UK HIG5.32
13Owner12Title1205/09/201466UK HIG6.02
Sheet1
Cell Formulas
RangeFormula
H2=IF(ISNUMBER(F2),F2&"."&TEXT(LOOKUP(F2,J$2:K$7)+COUNTIF(F$1:F1,F2),"00"),"")
 
Upvote 0
Does this help you (formula in H2 copied down)?

Excel 2010
BCDEFGHIJK
OwnerTitleDateCatCatCodeNoSuffixStart
Owner1Title1UK HIG
Owner2Title2UK HIG
Owner3Title3UK HIG
Owner4Title4UK HIG
Owner5Title5UK HIG
Owner6Title6UK HIG
Owner7Title7UK HIG
Owner8Title8UK HIG
Owner9Title9UK HIG
Owner10Title10UK HIG
Owner11Title11UK HIG
Owner12Title12UK HIG

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]05/09/2014[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1.18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]05/09/2014[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]05/09/2014[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]3.31[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]31[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]05/09/2014[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]4.71[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]71[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]05/09/2014[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]5.31[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]31[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]05/09/2014[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]6.01[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]05/09/2014[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1.19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]05/09/2014[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]2.08[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]05/09/2014[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]3.32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]05/09/2014[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]4.72[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]05/09/2014[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]5.32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]05/09/2014[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]6.02[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=IF(ISNUMBER(F2),F2&"."&TEXT(LOOKUP(F2,J$2:K$7)+COUNTIF(F$1:F1,F2),"00"),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Andrew Poulsom thank you for understanding my predicament and for such a prompt reply. I have replicated you layout by column with the only difference being that it F2 is F7 and so on. From what I am seeing it works with you but all I get is the [value_if_false] i.e. empty cell. Any idea if I am doing something wrong in applying your advice. Here is the adapted formula:

Code:
 =IF(ISNUMBER(F7),F7&"."&TEXT(LOOKUP(F7,J$7:K$12)+COUNTIF(F$6:F6,F7),"00"),"")

thank you
 
Upvote 0
What's the formula in F7?

I am sure you get this a lot but you have solved my problem by asking the right question. the following formula was in F7 and I forgot to take out the quotation marks, that is why your formula couldn't find a number.

Code:
=IF(E7=$L$12, "1 ", IF(E7=$L$13, "2 ", IF(E7=$L$14, "3 ", IF(E7=$L$15, "4 ", IF(E7=$L$16, "5 ", IF(E7=$L$17, "6 ", ""))))))

Slowly but surely I get the hang of it. In the meantime I thank you for your help. :beerchug:
 
Upvote 0

Forum statistics

Threads
1,223,151
Messages
6,170,381
Members
452,322
Latest member
CrimsonCoure

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top