Challenge - Pull Data From a Workbook and Then Some...

[Jenni5309]

I have a workbook that I enter fuel and mileage in for a fleet of about 20 semi trucks. It has a tab for each day of the month and a summary tab that totals for the entire month.

I am trying to pull data from that workbook into a new workbook to start tracking the weekly MPG...our customer is requesting this information.

I've started pulling data for the miles: =SUM('[July2014Fuelandmileage]6:12'!G57)...
and data for the gallons: =SUM('[July2014Fuelandmileage]6:12'!G116)...
then a simple division formula in the next cell...
BUT...the other workbook has to be open AND for each formula that pulls data over, it pulls up a box wanting to know where to get the data from, resulting in me locating the file and having to double click three times for each cell that I enter this formula in...this is a lot of double clicking!!!

I would like to come up with one formula that will take the miles (cells B57:V57) and divide it by the gallons (cells B116:V116) for tabs 6,7,8,9,10.11.12, for example, and put it in one cell beside each truck number. Is this possible???

I am not new to excel, but have never taken any classes to learn it either. Macro...? No idea...so please, if you reply to my post, be gentle

I am very grateful to anyone who can shed some light on this for me!

Thanks!

 

[FDibbins]

Hi.

1st, I would question the wisdom of having 1 sheet per day? The conventional way of doing thing kind of thing is to have all data on 1 sheet (sort of a data base), and then to use additional sheet/s to query/extract etc. This would eliminate the problem you have, although that SUM should still work across closed workbooks.

If it is asking for any additional info, then perhaps the file name and/or the sheet ref is wrong?
Recreate the formula again (using the mouse etc), and then compare that with what you already have?

 

[Jenni5309]

Thank you for your input. I wish I was more proficient with Excel...I'm quite embarrassed to say that I'm not sure what you mean by using the mouse etc. Do you mean for me to copy and paste the file name?

This spreadsheet was set up long ago by someone at our headquarters...I would love to change it, but I don't think they would allow it.

 

[FDibbins]

I'm not sure what you mean by using the mouse etc

Dont worry about it, we all have to start at the bottom

No, I meant make the formula again, from scratch, as if it never existed in teh 1st place. What Im trying to get to is for you to have a formula that works, so that you can compare it
with the formula that doesnt work.