MIleage reimbursement form formulas not working

Another board member was so helpful and assisted in cleaning up the charts I had created. The individual added the Ref-Mileage, Ref-Minutes and Ref-Dropdown sheets.
I’m in a pickle now because we’ve added new locations, and my formulas no longer work. If someone could please assist me, I would greatly appreciate it! I’m not savvy enough to complete this task on my own.

Mileage Log and Reimbursement sheet - There are some formulas in place but I do not think they are correct because I moved things around and added components on multiple sheets. The foundation is there, it’s just a matter or updating the formulas. I don’t understand them enough to troubleshoot.
1. The starting location C26 (and downward) and destination D26 (and downward) must populate F26 based on the sheet entitled Ref-Mileage. If “Yes” is selected in B26 this indicates roundtrip travel and the amount in G26 must be doubled.
2. The starting location C26 (and downward) and destination D26 (and downward) must populate H26 based on the sheet entitled Ref-Minutes. If Yes is selected in B26 this indicates roundtrip travel and the amount in H26 must be doubled.
3. The reimbursements noted in column G26 (and downward) must populate a tally up top by destination in F3-F11 and H3-H12 based on the destinations in D26 and downward. So for example, every reimbursement noted in G26( and downward) that coincides with a particular destination in D26(and downward)….let’s say Smithtown for instance, would tally in H11 which is the Smithtown box.
O
n the Ref-Dropdowns sheet:
1. C9 needs to be updated with Hauppauge (LIVM /MCM). I cannot figure out how to do it. The board member that spruced this chart up for me indicated I could hit refresh, but that did not work.

I am going to be continually adding locations to this spreadsheet so the Ref-Mileage, Ref-Minutes and Ref-Dropdown sheets will need to be updated in the future to support the new locations. I’d also have to add the location on the Mileage Log and Reimbursement sheet where the mileage reimbursement tallies by destination tallies are. I’d love if there is a way that I can just add whatever I need to without worrying about updating formulas to account for the new additions.

I just edited the response above.

My apologies. I misunderstood you. The fields up top are reimbursement tallies from (G26 and downward) by destination (D26 and downward). Here is an example. Does this help?
test.png

the file attachment was the same thing as the image that I pasted into the the thread.

There is no formula, that's the problem. I do not know how to create one. I can share a formula that was used in my version of this mileage form. here you go.

Awesome, thank you!

I'm stepping into a meeting, but will review this when I get into work first thing tomorrow morning. Thank you in advance for all of your assistance!!!!!!!!

We're almost there! I added two more rows (29 and 30) noting round trip travel for two starting location/destination combos that you had previously tested as one way so that I compare the populated results. I then noticed that the minutes in column H26 and downward are not doubling when "yes" is selected under round trip travel in B26 and downward. I highlighted the minutes in red that should be doubled.test.png

Correction to my last post (I could not figure out how to edit it)

We're almost there! I added two more rows (29 and 30) noting round trip and one way travel for two starting location/destination combos that you had previously tested so
that I compare the populated results. I then noticed that the minutes in column H26 and downward are not doubling when "yes" is selected under round trip travel in B26
and downward. I highlighted the minutes in red that should be doubled.

Thank you!test.png

Thanks so much! I looked at the formula that was in an old spreadsheet and tried plugging it in but it did not work. Here is it if you want to mess with it.

=IFERROR(INDEX(Minutes[#All],MATCH(C26,Minutes[[#All],[Mileage Minutes]],0),MATCH(D26,Minutes[#Headers],0)),0)

To get the minutes to double for round trips, try the formula below in H26 and fill down:

=IFERROR(IF(B26="NO",INDEX(Minutes[#All],MATCH(C26,Minutes[[#All],[Mileage Minutes]],0),MATCH(D26,Minutes[#Headers],0)),2*INDEX(Minutes[#All],MATCH(C26,Minutes[[#All],[Mileage Minutes]],0),MATCH(D26,Minutes[#Headers],0))),0)

glad to help, good luck!

I'm going to review this first thing monday morning!

The formulas look great! There is an error on the second sheet entitled Ref-Dropdowns that I cannot fix. The list of locations needs to be updated. I am hitting
refresh but nothing is happening. I need to update the locations below that I added stars next to.


Amityville
Bay Shore 180
Bay Shore 375
Brentwood
Deer Park
Garden City
Hamilton
***Hauppauge 110 Marcus Blvd
Hauppauge (LIVM / Middle Country Management)
Hempstead
Holbrook
***Huntington 281 Main
***Huntington 148 E Main
Little Neck
Port Jefferson
Riverhead
Sayville
Smithtown
Southampton
Southold
West Islip

Try this:

1) Click on the pivot table
2) Select the "Options" tab in your ribbon
3) Select "Change Data Source"
4) In the "Change PivotTable Data Source" userform, enter your Table/Range as: "mileage" (enter it without quotes). That fixed it for me.

I caught a little glitch but it was a simple fix. I think I am good to go.
I'm very grateful for the forums assistance. If I catch anything else that i cannot handle, I'll reply to the post. Thank you!!!!!!