More Fuel economy autofill challenge

[MLamb]

Wow, had this all typed up so well, then tried to add a table to it but it got goofed up and lost it all.... Oh well, so I try again!

Basic outlay/configuration:
I have a Excel 2013 workbook with a worksheet for each month that tracks all of a drivers activities, one of which is Fuel. The monthly worksheets contain the following columns needed to autofill the Fuel worksheet A date, B function (activity), E state, F Odometer, J Fuel (quantity). The Workbook contains a Fuel worksheet that provides Fuel usage information of each Fuel instance, monthly, and annually. The Fuel worksheet has the following columns A date, B state, C odometer, D Quantity, E calculates the MPG, and F comments for driver comments. There are many activities in the function column of the monthly worksheet and an undetermined number of Fuel instances, >0 but < 31; we have 30 rows for instances in each month of the Fuel Worksheet, more than enough. I would like the Fuel Worksheet to auto-populate itself with the data whenever a driver enters Fuel in the monthly worksheet, reducing the likelihood that a fuel entry is skipped or data entered incorrectly.

My approach/thoughts/attempt:
I either need a formula to grab the data of each instance for the entire Fuel row population in 1 formula, Or get it in combination 1 cell at a time. Simple is best, usually hardest to achieve because of overthinking! I think that CountIf may work best =COUNTIF(JAN!B2:B125,"Fuel", OK so far good start. We count the instances, but I need the first instance to retrieve data from JAN!A(n1), E(n1), F(n1), J(n1) and place them in the cells of the Fuel worksheet row2. The trick is then having the count increment for each consecutive instance, row 3 gets (n2), Row 4 gets (n3).... So i'm thinking that the formula in row 2 will be the working formula, and then in row2 it will be the same but do I put a +1 at the end to make it the second instance? Or am I going about this all wrong? I really need some assistance with the syntax as it relates to the usage. Am I on the right track for this?

Here's an example of the monthly worksheet:

Date	Function	Location	City	ST	odometer	miles	Driver	expense	fuel
1/6/2015	Rest	Home	Fayetteville	TN	803,536	119
1/7/2015	Load	Aladdin	Hendersonville	TN	803,655	107
1/7/2015	Stateline		I-65	AL	803,762	133
1/7/2015	Fuel	Petro	McCalla	AL	803,895	103		$300.00	80.043
1/7/2015	Stateline		I-59	MS	803,998	176
1/7/2015	Rest	Walmart	Gulfport	MS	804,174	5
1/8/2015	Unload		Gulfport	MS	804,179	17
1/8/2015	Trailer Repair	GulfCoastRepair	PassChristian	MS	804,196	55
1/8/2015	Stateline		I-10	AL	804,251	5
1/8/2015	Rest	TA	GrandBay	AL	804,256	317
1/9/2015	Fuel	Petro	DodgeCity	AL	804,573	86		$300.00	123.035
1/9/2015	Stateline		US-231	TN	804,659	7

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

And here is the corresponding portion of the Fuel worksheet:

Date	State	Odometer	Quantity	MPG	Comments
12/16/2014		803,396			CARRY FORWARD
1/7/2015	AL	803,895	80.043	6.23
1/9/2015	AL	804,573	123.035	5.51	idling

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Obviously my columns are not lined up like I'd like but you can see essentially what is happening. Currently I manually enter the data and the MPG is autocalculated.

Thank You in advance for any help!

 

[JonHaywood]

Hi,

Assuming your 'Fuel' sheet starts at cell A1 like this...

	A	B	C	D	E	F
1	Date	State	Odometer	Quantity	MPG	Comments
2	12/16/2014		803,396			CARRY FORWARD
3
4

<tbody>
</tbody>

...always leave row 2 free for your 'Carry Forward' value, then copy and paste these array formulae* into the following cells...

In cell A3

{=IFERROR(INDEX([I][B]Monthly[/B][/I]!$A$2:$A$[I][B]999[/B][/I],SMALL(IF([I][B]Monthly[/B][/I]!$B$2:$B$[I][B]999[/B][/I]="Fuel",ROW([I][B]Monthly[/B][/I]!$A$2:$A$[I][B]999[/B][/I])-ROW([I][B]Monthly[/B][/I]!$A$2)+1),ROWS($A$3:$A3))),"")}

In cell B3

{=IFERROR(INDEX(Monthly!$E$2:$E$999,SMALL(IF(Monthly!$B$2:$B$999="Fuel",ROW(Monthly!$E$2:$E$999)-ROW(Monthly!$E$2)+1),ROWS($A$3:$A3))),"")}

In cell C3

{=IFERROR(INDEX(Monthly!$F$2:$F$999,SMALL(IF(Monthly!$B$2:$B$999="Fuel",ROW(Monthly!$F$2:$F$999)-ROW(Monthly!$F$2)+1),ROWS($A$3:$A3))),"")}

In cell D3

{=IFERROR(INDEX(Monthly!$J$2:$J$999,SMALL(IF(Monthly!$B$2:$B$999="Fuel",ROW(Monthly!$J$2:$J$999)-ROW(Monthly!$J$2)+1),ROWS($A$3:$A3))),"")}

* hold CTRL + SHIFT and press RETURN to enter an array formula (the curly brackets will be added automatically - don't try to add them yourself)

NOTE: You will have to change each of the formulae to match the actual name of your 'Monthly' worksheet - I've called it "Monthly" in the examples above, but change these references to the actual name of your sheet.
Also, I've assumed your data will extend past the 12 rows you included in your example - again if you need to, change the end of the ranges from row 999 to whatever you feel is better.

Once you've adjusted the formulae to work you can select them and copy and paste down as far as you need.

Hope this helps

Jon

 

[MLamb]

Thanks Jon! This looks like it will work better than what I was stumbling through. I do have a couple questions for clarity. I'm seeing the range (and yes I'll have to change the ending point to match my sheet) but I don't recall the significance of $ symbol in the reference range: $j$2:$J$999 ~ what does it do? I'll play a bit with the formulas and let you know how it goes! Thanks again!

 

[MLamb]

WOW!!!!! works super well! changed what I knew I needed to and entered it in, whala! copied them down to fill the rest of the entries for the month in Fuel and worked like a charm. I have been struggling with this for about 6 months. Now I need to learn more about the functions you used.

Thanks!

 

[JonHaywood]

Glad I could help.
If you want to understand the formulae more it's probably best to break them down a bit.
The $ symbolises an absolute reference - basically, in your examples it fixes where a formula looks for or its data.
I always find it easier to see how a function works when you play around with some data...

	A	B	C
1	Multiplier	10	15
2
3	Sequence	Data set 1	Data set 2
4	1	7	11
5	2	11	8
6	3	4	10
7	4	6	11
8		=AVERAGE(B4:B7)
9		=B8*B1
10	5	12	8
11	6	7	7
12	7	3	10
13	8	10	6
14
15

<tbody>
</tbody>

The formula in B8 gives you the average of seq 1-4 of 'data set 1'.
The formula in B9 multiplies this average by the multiplier for that data set.

If you have time, have a play around, changing these formulae - when you edit the cell (double mouse click or press F2) you should see the references as coloured boxes. Either type $ symbols in the formula bar above the sheet, or experiment with pressing the F4 key (it cycles through different options of absolute references).

If you want a 'walkthrough', try these steps...

1. Copy and paste the formula from B8 into B14 - it should give you correct result (i.e. the average of cells B10:B13). None of the references are absolute so it will dynamically change the reference depending on where you paste it.
2. Paste cell B8 down to B15 instead - you should see =AVERAGE(B11:B14). Again, the references have all moved down one row because the formula has been moved down one row.
3. Copy and paste the formula from B9 into B15 (overwriting what you've just done) - you should see =B14*B7. The reference to B14 is correct, but because the reference isn't absolute it's no longer looking at cell B1 (the multiplier).
4. Go back to cell B9 and change the formula to =B8*$B$1. This basically says multiply the contents of cell B8 by the contents of whatever is ONLY in column B, row 1. It will never look at any other cell other than B1, so if you now copy and paste back into B15 the result should be correct (the average of the data multiplied by 10).
5. Copy cells B8:B9 and paste them into C8:C9 and C14:C15. The formula in C8 and C14 should give the correct averages (because the references have moved with the formula), but the formula in C9 and C15 are wrong - they've been forced to only use the 10x multiplier in cell $B$1.
6. Change the formula in B9 to =B8*B$1. This removes the absolute reference to column B, but still forces it to look across ONLY row 1. Again copy and paste this new formula, firstly into B15 (this should still give the correct result - you've moved the formula down, but it's always looking at row 1 so the calculation is still correct).
7. Then copy and paste it into cell C9 - you should now see =C8*C$1. Excel has moved the references from column B to column C (because you've moved the formula across a column) and it still forces it to look across row 1 (absolute).
8. Finally copy the formula from B9 to C15. Again, because the formula has been moved across one column, the column references have changed (from B to C). Where the row numbers are not absolute, the formula has changed because you've moved the formula down, but because the refernce to row 1 is ABSOLUTE it will only look at this cell (i.e. in this case C1).

You could add more data sets and multipiers to the table if you want and play around some more. I find it's a good way to build up understanding.


The other functions in the formulae do other interesting things but it's difficult to explain them all in one go. Also, what makes things a little more complex is the fact they are array formulae - these are slightly more advanced. It's best if you use excel help or search on the forum (or other websites) for other examples where the different functions are used - you'll probably find much better explanations than I could give!

 

[MLamb]

Thanks, Great help! I understand it much better now and I also added help to my ribbon. Not sure why it wasn't there...