Start and end date sorting

ProjectA

New Member
Joined
Sep 17, 2002
Messages
11
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,902
Members
414,110
Latest member
docops

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top