Fleet management.

[Mikew1977]

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

 

[Michael M]

Maybe you could post a small sample of your data ??
But you might need to look at a VLOOKUP function !!

 

[hiker95]

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

 

[Mikew1977]

https://www.dropbox.com/s/tafnqf9ugjow1l4/Truck template.xlsx?dl=0
here are my worksheets.
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.

 

[hiker95]

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.

 

[Mikew1977]

I can make what a complete worksheet should look like. Just need time

 

[L. Howard]

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]

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]

L. Howard,

Nicely done.


Mikew1977, here are two screenshots:

Excel 2007
	A	B	C	D	E	F	G
1	vehicle #		make		driver
2	registration #		model		vin #
3	reg expiration		year
4
5	service date	Atlas P.O#	service miles	service by	vendor invoice	maintenance	service
6				vendor	number	performed	cost
7	11/28/15	331920	6500	teds	17098	lof,alignment,wheel bearing	$25.00
8
truck #000 invoices

Excel 2007
	A	B	C	D	E
1			oil change hstory
2	vehicle #		make		driver
3	registration #		model		vin #
4	reg expiration		year
5
6	Date	Mileage
7	11/28/15	6500
8
truck#000 oil change history

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

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

 

[L. Howard]

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