Add cells from two different sheets with date variable

[klaimkilla]

Hi All! So I'm interning in the catering department of a hotel, and I would love some help streamlining one our reports that has to be done by hand. The first two images are from the same workbook, just different sheets/tabs.

This is our Catering Sheet. Once the event is over, we plug in the actual totals into Column F.

This is our Sales Sheet (Same workbook). We need to add the totals from Column F in the Catering tab (1st image) BY DATE with the totals in Column E BY DATE in the Sales tab.

Once we have those catering/sales totals by date, we enter them into a new workbook corresponding to that date in Column C (shown below).

We normally do this by hand. So help creating a formula would help a lot and make me look good!! Your help is very appreciated

 

[andiemac]

I wasn't positive which columns on the Catering and Sales tabs contained the actuals you wanted to add up, but here is a place to start:

=SUMIF(Catering!C:C,$A5,Catering!F:F)+SUMIF(Sales!C:C,$A5,Sales!F:F)

***this formula would go in your totals tab, in cell C5, and can be filled down.

 

[klaimkilla]

I am working with Column F on the Catering Sheet, & Column E on the Sales Sheet. The last image is where I need the actuals posted, which is located in an entirely different workbook.

Also, the values from the catering and sales tab need to be added by date. So all the catering/sales tabs for august 2 need to be added together.

 

[andiemac]

I would open both files before adding the formula to your totals location.

=SUMIF([OtherWorkbook]Catering!$C:$C,$A5,[OtherWorkbook]Catering!$F:$F)+SUMIF([OtherWorkbook]Sales!$C:$C,$A5,[OtherWorkbook]Sales!$E:$E)

You'll need to fill in the actual workbook name of where the Catering and Sales tabs live, ie [OtherWorkbook]

Leave the brackets as shown, just use the correct workbook name.

If the workbook name or path location changes often, you'll likely need some VBA to dynamically look for the source file.

 

[klaimkilla]

Doesn't work. And I'm not sure where in this formula it's calculating the totals based on date?

 

[klaimkilla]

Doesn't work. And I'm not sure where in this formula it's calculating the totals based on date?

It's saying the "The file name is not valid"...I'm entering in the file location "C:\Documents and Settings\bschrimple\Desktop\Christy".