Vlookup Help

[Tkawika]

I have an expense report I am trying to make stupid simple for my employee’s.
I have a worksheet called “Detailed Expense†they fill out. They input
their expenses and cost- code them. I have figured out a way to calculate
the total for each code selected (probably not the most efficient way) but
that works. I have another worksheet called “Final Expense Reportâ€. This
sheet has the totals for all codes in their appropriate categories. (ie:
Travel, Mileage, Hotels, Meals, Airfare, Per Diem) The issue : people
multiple have / work on multiple projects, they could potentially have four
different cost codes for Airfare. Each cost code used and its total must be
listed on the “Final Expense Report.†Totaling all the airfare codes is easy.
However, having each airfare code auto-populate in the final separate code
totals on the “Final Expense Report†has thrown me for a loop. I could just
put all the Cost Codes on the sheet and then have the total, but I don’t want
to put 300 Cost Codes on the “Final Expense Reportâ€â€¦
Any help is appreciated..

 

[J. Andrew Smith]

Two suggestions:

1. You can go ahead and list all the cost codes, but you could use data
filtering to suppress the ones with zero amounts.

2. You could group the cost codes by project or some other category, then do
totals based on the category.

 

[JR Form]

Tkawika,

You could use the sub total function in excel on your Final Expense Report
or better yet use a pivot table to produce the report.

JrForm

 

[Tkawika]

Thanks for the response. I thought about the filter idea and it would seem
to work with the users doing it. There should be a way with IF and Then
statements to create a list out of a list without repeating the items. For
instance:

When the employee puts the expensable item in the list, they would code that
item. IF I created a custom list out of that columns of CODES, Like relist
them in the Expense Report but only put the code once as so not to repeat.

So if you code three items as MEALS30002 and three other different codes in
the detail list. On the report side it list MEALS30002 once and the other
three codes below it. My brain isnt working so well anymore...

Does that make sense?