Start and end date sorting

[ProjectA]

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

 

[PaddyD]

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.

More info please!

Paddy