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:
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,
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,
