Creating a Custom Data Entry Form to Update Multiple Excel Records at Once

[Scotty81]

Hi,

I'm trying to make data entry easier in an Excel spreadsheet. As a simple example, I've got a data file with 2 columns of data: one for invoices and the other for month. So, in one year, there would be 12 rows for each invoice, assuming there was an invoice for each month. The data is great this way for pivoting, but that's about it.

What I'd need to do is create a form that shows one (or more) invoices down the rows and the different months (Jan-Dec) across the columns. In this way, I could enter expected invoice amounts for the entire year on one screen. Behind the scenes, I would want the form to be able to update the 12 rows of data (one row for each month of the same invoice).

In reality, I would want to group all my invoices by type. Let's say I had 3 phone invoices every month: long distance, local and wireless. I'd wnat to be able to filter on phone invoices and see the 3 rows of invoices be displayed for all 12 months.

Without resorting to anything too complex, and without going to an Access solution, does anyone know of a reasonable way to accomplish this?

Here is a very simplistic version of what I'd like to see:

Phone Invoices
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Long Dist
Local
Wireless

[Leith Ross]

Hello Scotty81,

Welcome to the Forum!

It would help to see a sample sheet. If you have confidential information on the sheet, you will need to either remove it or change before you post.

Sincerely,
Leith Ross

[Scotty81]

Leith,

For this issue, I came up with a non-proprietary example. Sheet1 shows the data as it really is. Sheet2 shows a pivot table, which indicates how I'd really like to view the data. While it doesn't look like there would be much difference between the 2 views, the difference is much more dramatic with many more rows of data. Also, you might wonder why I just wonder why I just don't use the pivot table view. The pivot table is fine for viewing the data. But, I want to be able to use this sort of view to be able to update the underlying data table. Since you can't update data from a pivot table, I need to come up with an alternate solution. Using some sort of custom designed user form was one way of thinking of getting around this issue.

Hopefully, the attached example will help clarify the objective I'm trying to achieve.

Thanks,
Scotty81

[royUK]

The easiest way would probably be a listbox to display all the entries. Select a line from the listbox , then populate relevant textboxes with the selected data, amend or add and copy input to the relevant tow.

[Scotty81]

In theory that would work. I could use a form to display and capture input and changes to various fields. The problem comes in when I want to select a different category of items. In my phone bills, I'd have 3 rows of data to display: long distance, local and wireless bills. But, if I selected another category, such as electricity, I might have only one line to display. Actually, it would be 2 rows: 1 for actuals, the other for expected costs.

One solution might be a dynamic user form. But, without knowing how many rows of data I could return, ti could be a bit of a pain to develop VBA for this situation (although I've seen some simple examples). Another option is to create a form that lets users select what data they want and then display those results in a pivot table. The only issue there is I'd have to copy the pivot table to be able to update those cells and then run some code to have the displayed data update the "data pile" behind the scenes. But, with the size of the pivot table variable (I might filter on 1 or 3 different criteria), entering in all those data values can get messy.

If I've missed something in understanding a simpler method that would work, please let me know.

Thanks,
Scotty81

[royUK]

You could filter the data using a combobox then populate the listbox with the filtered data

[Scotty81]

RoyUK,

Could you provide a simple example of what you are proposing as I am having trouble visualizing this solution.

Many thanks,
Scotty81

[royUK]

Here's an example.

You can filter the data using the comboboxes
Click on an item in the Listbox to populate the TextBoxes

I would suggest you add a column to contain a unique transaction ID this will then make it possible to write the textboxes contents to the sheet.

Edit: I have added reference numbers & code to amend the entries

[Scotty81]

royUK,

Thanks for the clarification. I can see that it is doable. Unfortunately, I won't take this route due to the amount of VBA code I'd have to modify to get a lot of other columns to display across the screen. (What I showed is a very simple subset of the data pile). But, what it does show me is that, besides your creative solution, it is not that there are many solutions out there that I've completely missed.

Thanks again,
Scotty81