0

I am trying to figure out how I can return a full range of information based on a date range and account information. I have a a spreadsheet that contains all the data outlined like this: enter image description here

I need to figure out how to get this information to an invoice page based on their account number and within a certain month. So sort of like this. The layout doesn't have to be the same, I just need it to group it together the same way, based on the criteria.

enter image description here

Is there any way of doing this? I don't know excel well enough to know what to google or use in order to perform this action.

Thanks in advance.

Rizowski
  • 3,528
  • 5
  • 22
  • 31

1 Answers1

1

Download the solution by clicking on this.

I have created a variable range called InvoiceDetails which will capture the range that corresponds to the given account number and month. You have to enter the account number and the month (Available in the source table) and hit the "Generate Invoice" button above it to have the invoices listed in the table below.

Do note that the input data in in Table format, do not change the same. And after pasting the values in the data sheet in the source table, do not forget to sort the same first on account number (A to Z) and then on the date (oldest to newest), without this you might have varied results generated.

For the example, I have used the data you have in your question screenshot, with a field inserted to capture the month in the source table.

When you feed in the month in the invoice sheet, make sure to feed in the first day of the month you want to insert, and only values that are available in the source table you filled in, else you will have errors. You can have validations set for this.

You this as an idea/ base to have your own workbook created.

Let me know should you have any bugs.

Or you can also use a simple pivot table to have these generated.

MS Sankararaman
  • 362
  • 3
  • 13