Complex Custom User Forms

[djl295]

I know this is a lot to ask so thank you very much if you even attempt/have attempted to help me with this.

I have a complex problem that I could use some help with. It is extremely long and difficult so if you have the time and generosity to help me I will be much In your debt.

I am designing a financial model which is capable of mapping a clients chart of accounts/trial balance (codes that describe each line item in a financial statement) to my companies chart of accounts (this is necessary because none of these clients use the uniform system of accounts). The mapping then uploads the clients data into a standardized financial statement (using sumifs() in order to eliminate unnecessary line item detail) which then acts as an input for the financial models tools.

This mapping system works via a 5 selections criteria that allow a user to identify a clients line item and concatenate(vlookup1,vlookup2,...,vlookupN) to create a trial balance (identifier code) that then matched with the clients line item trial balance code, and allows the information to be pulled into the correct line item in our financial statement (input sheet). In case this is unclear I have attached an example of this mapping procedure simplified that I found online. Copy of MappingTables.xlsx

Currently, the input sheet is fixed and massive (over 2500 rows) and allows for little customization. Does anyone have an idea to make this more dynamic and generate only the line items that are defined in the mapping system so that the user doesn't have to deal with such a massive form when not necessary.

To clarify. If the client's sheet has a line item, "Food and beverage expense" and our system of accounts calls that "F&B expense" I would map this using the descriptors (one in each column next to the line item). "Food&Beverage","Restaurant1","Expense","None","F&B Expense" which would generate the trial balance code: 120-100-150-000-5410 (the reason for such a complex descriptor system is because many departments could have the same line item (last code). In addition, this allows us to do subtotaling between departments by any of the descriptors, IE outlet type (restaurant1), metric type (expense), ect...).

Based on the above, is there a way that excel could read through the descriptor codes (or descriptor names themselves) and assemble the input sheet so that it contained: (1) A header labeled Food and Beverage, (2) a sub header labeled: "Restaurant1",a line item name(F&B expense), and (4) upload the correct account balance (say $50) next to the correct line item (as it already does). All this while keeping a good financial statement format (see below) and including subtotals.

Finally, if possible, it also needs to allow users to create custom line items and keep the account numbers associated with these line items uniform across several excel worksheets. So multiple users do not create the same custom line item under different numbers (ie. one creates "F&B expense" with line item number 5410 and another create it with the name F&B Expense and give it number 5420.

Good financial system format:

In General Form:

Department
SubDepartment
Metric Header ( in order of Revenue, Labor and Benefits Expense, Operating Expense)
Line Item Name
Totals and subtotals

In Non General Form:

Food and Beverage
Restaurant1
Revenue
Sale of Food
Breakfast Revenue
Lunch Revenue
Dinner Revenue
Subtotal
Sale of Beverage
Breakfast Revenue
Lunch Revenue
Dinner Revenue
Subtotal
Total Revenue Subtotal
Covers*
Sale of Food
Breakfast Covers
Lunch Covers
Dinner Covers
Subtotal
Sale of Beverage
Breakfast Covers
Lunch Covers
Dinner Covers
Subtotal
Total Covers Subtotal
Labor and Benefits Expense
Labor
Benefits
Subtotal
Operating Expenses
Silverware
Napkins
Subtotal
Total Expenses Subtotal

Profit (Total Revenue Subtotal-Total Expenses Subtotal)

*Incase you don't know, a cover is a single customer



I know this is a lot to ask so thank you very much if you even attempt/have attempted to help me with this.

[Kyle123]

It's difficult to recommend anything at all without a sample workbook


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[djl295]

The file is too large to upload on the forum, would you be open to letting me email it to you?

[Kyle123]

Nope, just simplify it. We don't need the whole thing. Presumably most of the sheet is made up of data we don't need to see? We only need a sample

[djl295]

Help Request Sample Workbook.xlsm

Here you go

The "Input Sheet" is the form that I would to tailor itself as needed. The "Mapping Chart and Trial Balance" is where the clients trial balance will be inserted and mapped using the drop downs on in the columns colored pink. The "Drop Down Keys" Sheet contains all relevant account identifier codes (this is what would need to be standardized across multiple copies of this model.

The pretext being that this each copy of the model will be used to handle a separate property, each financial statement (input sheet would benefit from being tailored to that specific client (as depending upon the size the client could have several thousand line items of just a couple hundred (as in the example in the 'Mapping...' sheet. The mapping is used to make data entry easy, once the mapping key have been filed out one time any new data can simply be copy and pasted into a new column in the 'Mapping..." Sheet to eliminate manual entry (after adjustments are made for the adding of new accounts).

Let me know if you need anything else


THANK YOU SO MUCH.

[djl295]

Also, columns containing identifier in the input sheet (B:E) will be hidden from the user.