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

Jenni5309

New Member
Joined
Sep 11, 2014
Messages
4
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top