data from date range

**schi13** · 03-20-2010, 03:22 AM

Hi,

I'm a beginner with excel and am trying to set up a spreadsheet.

On spreadsheet 1 I have a column that is the date and another column is how much I made.

On spreadsheet 2 I have two cells in which I want to input a "date from" and a "date to".

When I input dates into these cells on spreadsheet 2 I would like to be able to click on a button that will look up how much I made between these two dates and then output the value in another cell on spreadsheet 2.

I understand that I need to create a button and then assign a macro to it.

If anyone could help me with the vba code for this I would greatly appreciate it.

Regards
schi13

**royUK** · 03-20-2010, 04:00 AM

You don't need VBA to do this, the alternatives would be possibly a PivotTable or AutoFilter with the Subtotal Function. Attach an example workbook

**schi13** · 03-20-2010, 04:39 AM

Hi,

Please find example workbook attached. I have provided comments for your information.

Regards
Simon

**schi13** · 03-20-2010, 04:56 AM

By the way, I would prefer to do this via VBA code.

**royUK** · 03-20-2010, 05:32 AM

It should be done with formulas

**schi13** · 03-20-2010, 08:42 PM

Ok thanks could you please point me in the right direction of what kind of formulas I need to be looking at?

Regards
Simon

**Palmetto** · 03-20-2010, 10:10 PM

Here one approach. I create a couple of dynamic named ranges for the dates and sales and used then in the formula below.

=SUMPRODUCT(--(Dates>=N2),--(Dates<=N4)*(Sales))

Another option is to use DSUM, which requires passing the dates to criteria cells. The DFunctions are very efficient. See Excel Help on DSUM

**schi13** · 03-20-2010, 10:36 PM

Hi Palmetto,

Thanks for your reply. I am currently using this method however I am trying to avoid the lag when I input the dates. So I'm trying to put the dates in first then like click a button so that it runs the function then??? Any help with this would be appreciated??

Regards
Simon

**teylyn** · 03-20-2010, 10:41 PM

If you want to avoid long calculation times each time you enter a value, turn off automatic calculation and re-calculate after you've entered all your data by hitting F9.

Also, please update your user profile.You state you're using Excel 2003 but the file you attached was an xlsm, so obviously 2007 or later.