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

Jenni5309

New Member
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!

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

FDibbins

Well-known Member
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

New Member
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

Well-known Member
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.

Replies
5
Views
300
Replies
7
Views
404
Replies
0
Views
290
Replies
3
Views
508
Replies
3
Views
915

1,195,671
Messages
6,011,077
Members
441,581
Latest member
rp4717

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.

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

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