Sorting Report Data and Printing

ihurtado

New member
Joined
Nov 8, 2011
Messages
7
Reaction score
0
Points
0
There is that possibility is there that can help me with two tables
consist of the following fields:


Sheet 1:


This table is imported from a generated view in SQL, which is
aim is to generate a report with all related invoices,
payments and applied to each invoice notes and obviously the values
total outstanding of each invoice, which consist of the following
fields:

Sheet 2:
DESP DATE, DOCUMENT, VTO DATE, DATE
CURRENT DAYS, DETAIL, T. DOC, OFFICE, VAT, WB
NET, RETURN, DISCOUNT, PAYMENTS, BALANCE

The aim is the following, make a macro in which it can
select the number of the ID or identification number of the person and the system I
bring all documents (invoices, debit notes, credit notes, receipts).

I send the file to look at that I'm talking and I
indicate what would be the best option.

I've already been doing a filtering MSQUERY but as need is
generate a report as you can see on page 2, I have wanted to
automate with a macro that selecting the cedula of
person, I bring all documents and make it
corresponding operations.

what I really want is that you can select on page 2 customer NIT (cell H13) and the system I fill in the fields for operations for each invoice sheet 2 (debit notes to add, subtract and credit notescash receipts)
On page 3 to find the relationship of the cells between the two sheets.

On the third sheet is a table with the relationship between the tables of each sheet, the idea is that the sheet 2 in cell H13, you can select the customer NIT (extracted from the sheet 1) and the system will fill the fields I on page 2 with data from sheet 1 to perform the operations for each invoice (bring invoices, debit notes to add, subtract credit notes and cash receipts).

Note: on page 2 is the format that you want to generate


Thanks for your help
 

Attachments

  • Estado de cuenta_Sabado 20_00 (3).xlsx
    183.8 KB · Views: 27

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi there, and welcome to the forum.

I've attached a copy of your workbook that includes a PivotTable to summarize your data. You'll need to know two things about this:
1) if you change the filter in B14 to a different number (using the dropdown) the table will re-filter for you
2) if you update the data range on Hoja1, you will need to right click in the PivotTable and choose Refresh to get the data back in sync

The formulas in B9:B11 and others in the header are simply using an Index/Match combination off your data table to pull back the required info.

Hope this helps,
 

Attachments

  • Estado de cuenta_Sabado 20_00 (3).xlsx
    219.8 KB · Views: 29

ihurtado

New member
Joined
Nov 8, 2011
Messages
7
Reaction score
0
Points
0
Thank you very much, it's just what I needed and now I only need to multiply the values ​​by the "mccsignoclien" and get real value to be able to balance operations bills
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
You can add a calculated field to the pivottable to multiply items from the table against each other. I see the first, but don't see getrealvalueto in your data table...
 

ihurtado

New member
Joined
Nov 8, 2011
Messages
7
Reaction score
0
Points
0
I was reviewing your answer and I noticed that the filters are not bringing me all the information, for example:if you select the client 8045447 only brings me the document number 10, when the documents are actually 4 with the same number, one of which is the bill and the other 3 are the notes associated with the original invoice, I have been reviewing the table but did not see how the selection of the fields. I send you the file again to look at it.

thanks

 

Attachments

  • Estado de cuenta_Sabado 20_00 (3)-1.xlsx
    278.6 KB · Views: 20

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi there,

What version of Excel are you using? If you're in 2010, then click in the pivottable and go to PivotTable Tools-->Design-->Report Layout-->Repeat All Item Labels. (I can't remember if this is in 2007 or not.)

Now, that will get you part way.

If you check the last line, you'll actually find that it is actually the sum of three invoices, not just one. It's summing rows 10:12 as all the line items are identical except the amounts.

We can fix this in two ways:
1) We could move all the amount fields into labels... but that's going to mess up your ability to work with the numbers
2) You could adjust your original data pull to also include the record (index) numbers. If you do that, we can add them to the PivotTable ensuring that no records are compressed as they won't be seen as true duplicates.
 

ihurtado

New member
Joined
Nov 8, 2011
Messages
7
Reaction score
0
Points
0
The Excel version I'm using is 2007. I send you the file again to see what can help me
 

Attachments

  • Estado de cuenta_Sabado 20_00 (3)-1.xlsx
    66.6 KB · Views: 23

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Okay, so what I did here was:
  • Added a new field to your table (column A) that just lists the row of the worksheet. This gives as a unique value for each record, which means we don't have to worry about data compressing on us
  • Added the new field to the pivottable
  • Gave the new field a number format of 0 [White] to hide it in the table
  • Changed the field header to a blank space
I think that should do it for you. :)
 

Attachments

  • Estado de cuenta_Sabado 20_00 (3)-1.xlsx
    53.5 KB · Views: 30
Top