Forum Discussion
Find 1st non-blank field return field contents and get other contents from other fields in same row
Hi, I have tried many formulas and arrays and cant seem to find the right ones to accomplish my goals.
So in the attached excel file. I have data in my All Data worksheet. I am trying to run formulas so the Maintenance Worksheet is auto filled as data is entered on the All Data Worksheet.
On the Maintenance Worksheet First I want to check in Cell B15 Under Odometer, find the first non-empty cell in the Maintenance Column of All Data Worksheet and check if the number in the cell is between what is in Cell A15 and A16 under Service Inerval, if True, show the value, if false show empty or keep the field empty. Then I would Repeat the formula down the Column so when the number is between those two numbers it will show. When this is True, then I want to get the Date (Column A) from the same row where there was a value under the Maintenance Column and place it in the respective row in Column C (Date) on the Maintenance Worksheet, and get the Mechanic information from the same row under the Mechanic Column from "All Data", and the "X" marks and so on of the rest of the row to correspond to the row in the "Maintenance" Worksheet.
For Example the output data should look like
| Service Interval | Odometer | Date | Mechanic | PM Lube Oil & Filter | Brakes Front % | Brakes Rear % | Steering & Alignment | Lighting System | W/S Wiper & Heater | A/C & Heating System | Cooling Sys. Belts & Hoses | Exhaust & Emission Sys. | Battery & Charging Sys. |
| Replace Fuel Filter | Tires Front % & Wheels | Tires Rear % & Wheels | Remarks | |
| 6,000 | |||||||||||||||||||
| 12,000 | |||||||||||||||||||
| 18,000 | |||||||||||||||||||
| 24,000 | |||||||||||||||||||
| 30,000 | |||||||||||||||||||
| 36,000 | |||||||||||||||||||
| 42,000 | |||||||||||||||||||
| 48,000 | |||||||||||||||||||
| 54,000 | |||||||||||||||||||
| 60,000 | |||||||||||||||||||
| 66,000 | |||||||||||||||||||
| 72,000 | 77120 | 10/25/2018 | Pep Boys | X | X | Oil Change... | |||||||||||||
| 78,000 | |||||||||||||||||||
| 84,000 | |||||||||||||||||||
| 90,000 | |||||||||||||||||||
| 96,000 | |||||||||||||||||||
| 102,000 | |||||||||||||||||||
| 108,000 |
I was trying a Find, Lookup formula, or Index/Match combination, but couldn't get it to work right. Your help is greatly appreciated.
