Date Lookup and Data Collections

[YorkeG]

Afternoon,

I am looking for a spreadsheet that can allows me to enter in order amounts and pull out daily totals into a chart.

On the Tuesday to Friday sheets of the attached file, I would like to be able to enter different sales orders into the chart, so I can see a daily total of sales through out the month. This means I need a formula to go into the Monthly Data sheet that will pull out the totals for the matching dates.

For example, if I entered in sales orders in the Tuesday sheet, the B4 cell in the Monthly Data sheet would read the total amount in H33 of the Tuesday sheet.

I need to know the formula that will do this.

Hope this makes sense. Contact me if you need clarification.

Yorke

[ChemistB]

Okay so on Tuesday Sept 1, you open up the Tuesday Tab, put in the date B4 and enter all your orders for the day. The final value is automatically moved to B4 of Monthly Data. Yes? That part is easy. However, next Tuesday, you replace 9/1/2015 with 9/8/15 and clear the form. Poof, the value in B4 of Monthly disappears with it.

You can either
1. Make sure you copy>Paste Special Values in Monthly Data before erasing the data or
2. Use VBA to move the values to Monthly Data instead of formulas.

[rcm]

Are you going to keep a daily tab for each day?

[YorkeG]

Yes, I did wonder that myself.

I was going to copy out the numbers and paste as special values, but how difficult is VBA to do this???

Because after 9/1/2015 becomes 9/8/2015, the value will have to go into I4 not B4 too right?

I appreciate your help on this.

[YorkeG]

i wasn't going to keep a tab for each day, just one for each day of the week, then restart the sheets on a new week....

I can do whatever though...

[rcm]

Please Login or Register  to view this content.

if you run this macro weekly you could change the dates and keep only the 4 tabs

[YorkeG]

could you add this to the attached spreadsheet and send it back? I have no idea how to insert codes...i struggle with excel!

notice that i added a Monday tab on the spreadsheet.Daily Sales In.xlsx

[rcm]

I added the 31/08/2015
press ctr p to run the macro