I have a list of about 800 purchase invoices. All are in one column in a worksheet. In the same or another worksheet,I need a recommendation on how to build a rows report based on those prelisted PI's. The rows report will be a purchase invoice lines report.
Anyone have any recommendations? Once I figure this out I'll share the ultimate result.
Date
Votes
2 comments
-
Jet Reports Historic Posts I have done something like this in some of my reports -
You can actually build your ROWS command in a new Worksheet - based on the range in your Sheet with the Items (here I've called that sheet ITEM LIST).
=NL("ROWS=2",'ITEM LIST'!C5:C805)
Then add your second ROWS command to pull the lines based on each Invoice Number
in case you have not done a report with two ROWS commands - you need to know that each ROWS command should be in it's own row on the worksheet, so your structure would look something like this:
Cell B3 =NL("ROWS=2",'ITEM LIST'!C5:C805)
Cell C3 =B3 (to reference the Invoice number)
Cell C4 = NL(ROWS,"INVOICE LINE","LINE NO.","INVOICE",C3) <- note, I'm writing this on the fly, and probably don't have the table or field names fully correct, please modify as needed with your table and field names.
If you don't want to total the Invoice itself, then you can HIDE row 3, and then your report will only show the invoice lines, and not a line for the total invoice.
The one thing to keep in mind is that your first ROWS command is not a true Record Key - so any information you want from the Invoice number will have to be pulled using NL("FIRST" commands referencing the Invoice Number rather than NP commands.
Please let me know if you need more information! -
Jet Reports Historic Posts Thanks heather! Thats a pretty elegant solution. I found a different route a few hours ago with some excel tricks, but I'll definitely remember that. This will definitely come up again.
By the way I'm building a cash flow forecast. I found a way to relate checks back to the original purchase invoice. Its not perfect, but its about as close as you can get without building a worksheet that takes an hour to process and crashes excel. If you want a copy let me know your email address and I'll send it over.
Please sign in to leave a comment.