Filling in the forms with data from the table

Formulation of the problem

We have a database (list, table – call it what you want) with information on payments on a sheet Data:

Task: quickly print out a cash receipt (payment, invoice …) for any desired entry selected from this list. Go!

Step 1. Create a Form

On another sheet of the book (let’s call this sheet Form) create an empty form. You can do it yourself, you can use ready-made forms, taken, for example, from the websites of the Chief Accountant magazine or the Microsoft website. I got something like this:

Filling in the forms with data from the table

In empty cells (Account, Amount, Received from etc.) will get data from the payment table from another sheet – a little later we will deal with this.

Step 2: Preparing the payment table

Before taking data from the table for our form, the table needs to be slightly modernized. Namely, insert an empty column to the left of the table. We will use to enter a label (let it be the English letter “x”) opposite the line from which we want to add data to the form:

Step 3. Linking the table and form

For communication, we use the function VPR(VLOOKUP) – you can read more about it here. In our case, in order to insert the number of the payment marked “x” from the Data sheet into cell F9 on the form, you must enter the following formula in cell F9:

=VLOOKUP(“x”,Data!A2:G16)

=VLOOKUP(“x”;Data!B2:G16;2;0)

Those. translated into “ understandable”, the function should find in the range A2: G16 on the Data sheet a line starting with the character “x” and give us the contents of the second column of this line, i.e. payment number.

All other cells on the form are filled in the same way – only the column number changes in the formula.

To display the amount in words, I used the function Own from the PLEX add-on.

The result should be the following:

Filling in the forms with data from the table

Step 4. So that there are no two “x” …

If the user enters “x” against multiple lines, the VLOOKUP function will only take the first value it finds. To avoid such ambiguity, right-click on the sheet tab Data and then Source text (Source Code). In the Visual Basic editor window that appears, copy the following code:

Private Sub Worksheet_Change(ByVal Target As Range)  Dim r As Long  Dim str As String    If Target.Count > 1 Then Exit Sub  If Target.Column = 1 Then      str = Target.Value      Application.EnableEvents = False      r = Cells(Rows.Count, 2).End(xlUp).Row       Range("A2:A" & r).ClearContents      Target.Value = str  End If    Application.EnableEvents = True  End Sub

This macro prevents the user from entering more than one “x” in the first column.

Well, that’s all! Enjoy!

  • Using the VLOOKUP function to substitute values
  • An improved version of the VLOOKUP function
  • Amount in words (Propis function) from the PLEX add-on

 

Leave a Reply