Hi all,

I've not attached the document referred to below as it is simply too large, but my query I think can be easily understood. But first, a bit of background...

I'm writing with respect to a document I've developed over the last few weeks for the purpose of presenting management accounts. I'm currently working in a group of companies that has a poor ERP system whose financial statements output is sub-standard to say the least. I've since proposed to senior managers, presenting the monthly financials in Excel format until such a time as we implement an ERP capable of generating satisfactory statements directly. This excel document requires the export a trial balance from the ERP and paste in the excel document and doing so will automatically update the financial statements in the excel document.
A one-off task of assigning each trial balance code is required, in order for that nominal account to be placed in the correct location. Let's take an example of the first time someone will ever paste information into this empty document. Let's also say the first trial balance consists of only one single nominal account code' '100021 - Salaries'. When the user pastes this trial balance into the document, the document itself won't whether '100021 - Salaries' belongs on the profit & loss account or the balance sheet, is a revenue account, an expense account, a liability account etc. So I've built a functionality into the spreadsheet whereby all new codes must be assigned a number of 'classes' before they can appear on the financial statements e.g. for '10021 - Salaries' the user will assign each nominal code a series of classes from drop-down (data validation) lists that are based on pre-set choices. For '10021 - Salaries', the choices are as follows (selections in bold):
- Class 1: P&L or BS
- Class 2: Revenue or expenditure
- Class 3: Direct cost or overhead
- Class 4: List of all comprehensible expenses (e.g. accountancy fees, bank charges, fuel etc. Salaries will be selected from this list)

It is in the best interest of all stakeholders that the group use a standardised financial statements format, hence my documents will be used by all companies in the group. However, since each company provides completely different services, it is necessary that the document include some function whereby the finance manager of each business unit can tailor the document to their company e.g. revenue streams of company A will differ from revenue streams of company B. Because each company will have it's own unique revenue streams and direct costs, I cannot fix any direct costs into the data validation lists (in the same way I can fix overheads) since the direct costs of one company won't be relevant to another. Hence, I have created a function whereby users can manually type their own company's revenue streams and direct costs into a 'freeform' box in the document, and these options will then appear in the data validation lists. So far, all is working well and no issues.....

So as you can see from the above, revenue streams and direct costs will be entirely dictated by the finance manager of each business unit. With respect to overheads however, I have a fixed data validation list that cannot be changed by the user, since overheads should largely be similar across all companies and therefore no need to be tailored. This list consists of approximately 40 expense types e.g. accountancy fees, audit fees, bank charges, depreciation, fuel, salaries, stationery etc.
What I'm finding however, is that there are small number of overheads that can be specific to a company. For example, one of the companies in the group is a laboratory and incurs accreditation fees annually. To include 'accreditation fees' in the fixed overheads list isn't acceptable however, since none of the other companies in the group incur accreditation fees and, since all companies will use this document, including 'accreditation fees' only serves to 'litter' the data validation list (from the perspective of the other companies) since this option will always be irrelevant.
With this in mind, I would also like to allow the user to add additional overhead expenses for their company (on top of the fixed options) HOWEVER, the risk here is that allowing users to insert their own additional codes also opens up the possibility of duplicating codes. For example, although 'accountancy fees' is a fixed overhead option, the user may miss it in the list [while assigning classes to each code], at which point the natural response would be for the user to create an account for 'accountancy fees'. They may call it 'accounting costs' [or some other variation].

I don't want users to add expense accounts that already exist in the fixed list (whether or not the names of the accounts are exactly the same, or just similar), as this too will also 'litter' the list. I therefore would like to know if it is possible to prompt excel to generate predictive options to minimise this risk.

For example, if the user does begin to manually add an 'accounting costs' overhead option via the freeform box, at the point of typing 'acc', or 'acco' or 'accou' (etc.), Excel will generate a 'predictive text' option based on similar results in the pre-set list (in the same way that Excel generates predictive text when I repeat the same words in the same column - for example if I type the word 'Hello' in cell A1 of a spreadsheet and then proceed to type the letter 'H' in cell A2, Excel will predict that I want to repeat the word hello, by generating the untyped element of the word i.e. 'ello' in a grey highlight. If I wish to repeat the word 'Hello' in cell A2, I don't then have to type the remainder of the word, I can just press ENT and the word is generated for me).
In this case, I don't to allow the user to press ENT and add the code in the freeform box, since the code already exists in the pre-set list. I just want Excel to show the predictive option so that the user becomes aware that an account code exists already. If they do proceed by pressing ENT, then Excel should auto-generate the option, it should block it.

Sorry for the long post. Any advice welcome.