Dynamic Formula To Keep A Column of Data alligned with a pivot table

[BigDawg15]

In this file Vehicle Fuel Tracking.xlsm I have a pivot table set up to filter my data. Next to the pivot table I have a column that Totals the Mileage based on the MAX and MIN of each group. I am looking for a dynamic formula to keep the totals alligned if data is added or deleted from the pivot table.

I would also be open to changing the data table to accomodate this request if needed.

Thank you in advance for your assistance,

BigDawg15

[MarvinP]

Hi BigD,

Find my example. You need to use a Dynamic Named Range for your data table. Then create the Pivot Table using this DNR. Then put an event macro on the data sheet to refresh the PT if/when anything changes. All this is in my example. Hope it helps with your problem.

[BigDawg15]

MarvinP,

Thank you for the reply. I will see if I can use your example to make mine work. I may come back for a little more assistance, but it will have to wait till later. Getting late now.

Thank you again and reputation added,

BigDawg15

[BigDawg15]

MarvinP,

Please see attached. Vehicle Fuel Tracking.xlsm I was able to partially get it working by following your example however, I have to sort the data first and the total formula is not dynamic so I am missing something. It appears I need a dynamic formula on the data sheet.

Thank you for your time and assistance.

BigDawg15

[BigDawg15]

Bump.

Anybody have an idea?

Thank in advance,

BigDawg15

[MarvinP]

Hi BigDawg,

You were so close to having it perfect.. See the attached where:
1. I added a macro called RefreshPivots that simply refreshes all pivot tables in the workbook.
2. An EVENT Macro that will fire if anything changes on Sheet1. You need to look behind Sheet1 in the Macro to see it.

This should be the final answer for you. I hope. If not keep asking. I'm impressed that you found and created the DNR and based the PT on it.... Good Job.

[BigDawg15]

MarvinP,

Thank you for the kudos and patience. You gave good guidance and example to follow. I love excel and continue trying to learn it every day. I am understanding the data to pivot refresh. Maybe I am not explaining my final problem or understanding your solution correctly. When looking at the data sheet in the last workbook you attached, you see the data is mixed with vehicle numbers and corresponding data by date.

Unless I'm missing something in a previous post (and it is very possible), there is a different formula needed in column F (dynamic or otherwise) than what I have, to calculate the mileage for each vehicle number without having to sort the data (or does the data have to be sorted)?

Thanks again for your assistance and patience.

BigDawg15

[MarvinP]

I believe the data needs to be sorted first. The formula in column F needs to get increasing odo reading without going backwards. I didn't work on Col F in the last attachment. I only put in the Pivot Refresh and the On_Change code to refresh whenever something on Sheet1 changed.

I'll need to look later as the dentist calls.

[BigDawg15]

No problem. Thanks again for your patience and assistance.

BigDawg15

[MarvinP]

Hi BigDawg15,

Is this problem done or do you need something more with it? Let me know what exactly goes wrong, if anything.

It would be great if you could edit your original thread and change the Prefix of the Title to "Solved" if you don't need anything else.

[BigDawg15]

MarvinP,

I am still looking for assistance with a dynamic formula for column F on the data sheet (Sheet1) that will calculate the total mileage from the max and mins odometer of each group if more data is added.

If a formula is not possible, I'm open to a macro. If you want me to start another thread for that I will, but this is all still related to getting the data to the pivottable.

I hope that is clear.

Thanks again for your assistance.

BigDawg15

[MarvinP]

OK BigDawg,

Find the attached where sheet1 in column F has the total mileage formula. You can add more data and it will recalculate automatically. When you enter a new VRN or ODO some of the data will look bad until you also enter the other field. I've created an ARRAY formula to get the total mileage. You need to enter it with a Ctrl+Shift+Enter (CSE) keystroke combination.

http://www.cpearson.com/excel/ArrayFormulas.aspx

Hope this does it for you. Let me know if you need more.

[BigDawg15]

MarvinP,

Thank you again for your assistance. I can work with that.

Regards,

BigDawg15