# Start and end date sorting

#### ProjectA

##### New Member
This is my first posting on the message board, so bear with me. I have a spreadsheet with two worksheets, DataSource and Summary. The DataSource has the following column headings: Company, product, country, cheque payment, cheque date, date banked, invoice number, invoice date, usage start, usage end date, amount.
In the summary sheet I have used a offset/indirect direct method to rip out payment totals by company, by product. Unfortunately, this method has run into trouble when trying to evaluate if a complete month has been paid for and how much. For example, company A pays \$1000 for usage 1-jan-02 to 31-jan-02. In the summary sheet the table shows month start and end dates are running down with company across. In the intersect for january and company A, should read \$1000 PAID. If the usage date was 1-jan-02 to 15-jan-02. It should read in the summary, \$1000 unpaid 16 days. Please seee examples below of methods I have tried so far.
{=MATCH(startdate,VLOOKUP(company,COUNTA(OFFSET(DataSource!N7,0,0,10000,Datasource_startdateRange),Datasource_startdateRange,FALSE),0))}

=IF(OFFSET(INDIRECT(DataSource),0,company&startdate,activeRows,1),company&startdate,if(OFFSET(INDIRECT(\$L\$8),0,company&enddate,DataSource,1),company&enddate,(INDIRECT(\$L33),0,13,\$M33,1)),""),0)

Both these methods are resource hungry, painful to edit/don't work.

There must be an easier way? Please advise on method of manipulation of the datasource rather than direct formula help. If there is a quicker VBA method, that would good.

regards,
Justin

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi - welcome to the board!

A few days since you posted this & no answer! I guess that's because, despite a fairly detalied description, it's still not entirely clear what outcome you're after (especialy when your formulas refer to labels / named ranges!)

Care to post back with (a) a snapshot of the relevant data (use the html maker addin available from the link below this post). Only post as much info as we'd need to get an idea of the problem, not all the rows / columns in the data! (b) a description of the expected results, based on the data you post. Include a 'natural language' description of the calculation you need to perform.

Replies
6
Views
294
Replies
5
Views
390
Replies
1
Views
2K
Replies
0
Views
784
Replies
9
Views
629

1,196,318
Messages
6,014,614
Members
441,832
Latest member
tony tessman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back