0

Trying to create a formula that will search 50 tabs for a specific person, between specific dates and total miles traveled.

I Have 50 tabs identified by truck number; on each tab the columns A through D are set up as Driver Last Name, start date, end date and miles traveled. They run 4 to 5 trips a month. I need a sheet that shows Drivers in Column A, Jan in B, Feb in C and so on to December in column Z and the last column total for year.

I do not know how to search for the name, select miles from column d if they fall within a specified month and have it show on the total sheet.

Can you help?

  • Look at VLOOKUP. – fixer1234 Feb 14 '19 at 7:28
  • Welcome to Super User.  It would appear that you have accidentally created two accounts.  This will interfere with commenting, editing your own posts, and accepting an answer.  You should use the contact form and select “I need to merge user profiles” to have your accounts merged.  In order to merge them, you will need to provide links to the two accounts.  For your information, these are superuser.com/users/998127/JMP and superuser.com/users/998587/JMP.  You’ll then be able to edit your question. … … … … … … … … … … … … … P.S. Please register your merged account. – Scott Feb 15 '19 at 6:05
  • Since every driver takes more than one trip per month and you are looking for total miles traveled per month therefor you need SUMIF based on month ,, ☺ – Rajesh S Feb 15 '19 at 9:54
0

I have been messing around with VLOOKUP however I am not having luck. I know I am making it harder than it is, when I enter the function and go through the steps it says I either have too many arguments or not enough arguments.

I set three tab titled 1, 2, 3, and Totals

I put three names in column A on each sheet. Columns B and C are titled Start and End and contain dates in within the month of January and column D is titled Miles and I have entered numbers in the cells.

I have a sheet titled Data with all the driver names that I titled DriverName so it can be used in the formula. I used the VLOOKUP and chose the first name in the list as the one to look for (A2) searched all three tabs A2:A10 for the name asked it to grab data from D (4) sometimes it comes up zero and others it shows argument errors.

I need it to search column A in all three sheets for specific names; then if name found it needs to search columns B & C for dates between 1/1/19 and 1/31/19 then add all amounts in column D together that meet the search criteria and display that total next to the corresponding name in the January Column (A) on the totals sheet.

I don't think I am grasping the formula entries needed.

  • Welcome to Super User.  It would appear that you have accidentally created two accounts.  This will interfere with commenting, and editing your own posts, and accepting an answer.  You should use the contact form and select “I need to merge user profiles” to have your accounts merged.  In order to merge them, you will need to provide links to the accounts.  For your information, these are superuser.com/users/998127/JMP and superuser.com/users/998587/JMP.  You should then edit your question to include the above information. – Scott Feb 15 '19 at 6:05
  • In the sites Q&A format, answers are reserved for solutions. All of the information about the question needs to be edited into the question. When you do that, keep in mind that it is hard for other people to visualize what you have in a spreadsheet based on a complicated verbal description. A picture's worth a thousand words. Add a screenshot or mock-up to your question so people can understand what you're trying to do. If you need to pull details from many tabs, VLOOKUP, at least by itself, may not be enough, so people need to understand how everything fits together. – fixer1234 Feb 15 '19 at 7:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.