Forum Discussion

Suzanne_01's avatar
Suzanne_01
Copper Contributor
Jul 17, 2019

Using Microsoft forms to create an invoice in excel.

Hi,

 

I made a form in Microsoft Forms in which orders can be placed for within the company. The goal is to have the data from a new form automatically added to a Pivot Table in which the data of the prices are also put. I have a few problems:

1. When calculating the total price, you can only seem to use two specific columns, but I'd like to have one column differentiate as I add or delete it from the pivot table.

2. How am I supposed to make a macro in which data from Microsoft Forms is added as a form and is filled in in the pivot table?

3. One question is from which store the order came in, can I add this automatically to the invoice when the form is filled in?

 

Thanks in advance for a response,

 

Suzanne Kepel

excel
Formulas and Functions
office 365

4 Replies

Sort By
      • Roger Govier's avatar
        Roger Govier
        MVP
        Jul 17, 2019

        Suzanne_01 

         

        Hi Suzanne

        Using a Pivot Table to generate an Invoice in this case, is not the best solution in my opinion.

        In the attached file, which has been saved as a .xlsb as it contains two small macros, I have inserted 2 new sheets called Extract and Invoice.

         

        Extract uses Advanced Filter to extract any given Order Number from your Tale 1 on Sheet 1.

        Invoice uses the Transpose() function to turn the Extracted data from Horizontal to Vertical, and then uses Index() , Match() to pull through the price from your table 2 on Blad2 and then multiplies these by quantity to produce the line Totals which get Sumed at the end and have tax added.

         

        Sheet Invoice has some event code which gets triggered only when you change the Order number in cell C2

        Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$C$2" Then
        With Application
        .EnableEvents = False
        .ScreenUpdating = False
        End With
        Call FilterData

        End If
        With Application
        .EnableEvents = True
        .ScreenUpdating = True
        End With
        End Sub

         

        If there is a change then the FilterData code in module 1 gets invoked to change the data that Advanced Filter Extracts to sheet Extract, and then used on sheet Invoice.

         

        Sub FilterData()
        Sheet1.Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheet5.Range("A1:A2"), CopyToRange:=Sheet5.Range("A5:S5"), Unique:=False
        End Sub

         

        I hope this helps.

Resources