Fleet management.

Mikew1977

New Member
Joined
Nov 28, 2015
Messages
12
How would I be able to have excel recognize lof (lube oil filter) , amongst other words in a cell, and then have it copy the date from a cell and the mileage from a cell, to another worksheet?. I have tried but can't get it to work. Any ideas? Please. Thanks
my boss is waiting for a solution so I don't have to do it on paper any more!
All of the figure's are in one row 7
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,963
Office Version
2013
Platform
Windows
Maybe you could post a small sample of your data ??
But you might need to look at a VLOOKUP function !!
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Mikew1977,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) for the results that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG/IMG file, or, flat text) try one of the following:

There are several methods. Here are 3 for you to investigate.

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

To test the above:
Test Here


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Mikew1977,

Thanks for the workbook/worksheets.

1. The only two worksheets in your workbook, truck #000 invoices, and, truck#000 oil change history, are for one truck?

2. Can we see more examples of the raw data in worksheet truck #000 invoices?

And, so that we can get it right on the first try:

3. Can you manually complete worksheet truck#000 oil change history, with the results that you are looking for?

Then, post the new workbook/worksheets to dropbox.
 
Last edited:

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
https://www.dropbox.com/s/tafnqf9ugjow1l4/Truck template.xlsx?dl=0

in cell A7 if lof is present then i need the data in cells A7 & C7 to be copied to Sheet( Truck Oil Change Hstory )
Date in A7 Milage in B7.
I also would need to be able to copy this formula multiple times.
Hi Mikew1977,

I presume the red text is a typo and you mean F7 on truck #000 invoices sheet.

Try these two formulas on 'history sheet':

Cell A7 =IF(COUNT(FIND({"lof"},'truck #000 invoices '!F7))>0,'truck #000 invoices '!A7, "")

Cell B7 =IF(COUNT(FIND({"lof"},'truck #000 invoices '!F7))>0,'truck #000 invoices '!C7, "")
Then pull formulas down.

Howard

Note, there are two following spaces in sheet name 'truck #000 invoices
 

Mikew1977

New Member
Joined
Nov 28, 2015
Messages
12
Hi Mikew1977,

I presume the red text is a typo and you mean F7 on truck #000 invoices sheet.

Try these two formulas on 'history sheet':



Then pull formulas down.

Howard

Note, there are two following spaces in sheet name 'truck #000 invoices
there is a problem with the formula they dont work. the( are red, excell says its not a formula
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
L. Howard,

Nicely done.


Mikew1977, here are two screenshots:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">vehicle #</td><td style="text-align: center;;"></td><td style="text-align: center;;">make</td><td style="text-align: center;;"></td><td style="text-align: center;;">driver</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">registration  #</td><td style="text-align: center;;"></td><td style="text-align: center;;">model </td><td style="text-align: center;;"></td><td style="text-align: center;;">vin #</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">reg expiration  </td><td style="text-align: center;;"></td><td style="text-align: center;;">year</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">service date</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Atlas P.O#</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">service   miles</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">service   by</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">vendor   invoice  </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">maintenance  </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">service</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">vendor  </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">number  </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">performed  </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">cost</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-top: 1px solid black;;">11/28/15</td><td style="text-align: center;border-top: 1px solid black;;">331920</td><td style="text-align: center;border-top: 1px solid black;;">6500</td><td style="text-align: center;border-top: 1px solid black;;">teds</td><td style="text-align: center;border-top: 1px solid black;;">17098</td><td style="text-align: center;border-top: 1px solid black;;">lof,alignment,wheel bearing</td><td style="text-align: center;border-top: 1px solid black;;">$25.00</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:12.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">truck #000 invoices </p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">oil change hstory</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">vehicle #</td><td style="text-align: center;;"></td><td style="text-align: center;;">make</td><td style="text-align: center;;"></td><td style="text-align: center;;">driver</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">registration  #</td><td style="text-align: center;;"></td><td style="text-align: center;;">model </td><td style="text-align: center;;"></td><td style="text-align: center;;">vin #</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">reg expiration  </td><td style="text-align: center;;"></td><td style="text-align: center;;">year</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">Date</td><td style="text-align: center;;">Mileage</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">11/28/15</td><td style="text-align: center;;">6500</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:16.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">truck#000 oil change history</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A7</th><td style="text-align:left">=IF(<font color="Blue">COUNT(<font color="Red">FIND(<font color="Green">{"lof"},'truck #000 invoices '!F7</font>)</font>)>0,'truck #000 invoices '!A7, ""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=IF(<font color="Blue">COUNT(<font color="Red">FIND(<font color="Green">{"lof"},'truck #000 invoices '!F7</font>)</font>)>0,'truck #000 invoices '!C7, ""</font>)</td></tr></tbody></table></td></tr></table><br />

Range("A7").NumberFormat = "mm/dd/yy;@"
 
Last edited:

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
there is a problem with the formula they dont work. the( are red, excell says its not a formula
Try this.

Enter in cell A7 =IF(COUNT(FIND({"lof"},'truck #000 invoices '!F7))>0,'truck #000 invoices '!A7, "")

Then pull the formula down as far as you need.

While ALL the just pulled down cells are still selected, press f9.

Then do the same with this formula in B7

Cell B7 =IF(COUNT(FIND({"lof"},'truck #000 invoices '!F7))>0,'truck #000 invoices '!C7, "")

Pull down and f9

Howard
 

Watch MrExcel Video

Forum statistics

Threads
1,102,302
Messages
5,486,058
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top