Hi,
I’m attempting to create a spreadsheet for maintenance atwork, but having problems getting what I’m looking for. I have a Data Input page where I have myvehicles in the rows and inspections & date of inspection in thecolumns. I manually input the dates forthe inspections. I’m trying to create aquick reference sheet that automatically shows the upcoming inspections. I figured out how to make excel show me whatis coming due within the next XX days. The issue is this, there won’t be enough space to have individual cellsdedicated to all my possible inspections on my reference print out. I want the sheet to list only the inspectionsthat are due in the next, say 10 days. Ihave, 40 possible inspections per vehicle, but I know that no more than 15 willcome due for the same vehicle at the same time.
I have the data input sheet, I also have a nearly identicalsheet with one row beneath the dates that looks at dates and puts a “1”directly below the corresponding date if it is within 10 days from TODAY. On my desired “quick reference print outsheet,” I used multiple “IF” functions to reference the row with the “1”’ init, and it would populate the inspection.
-I used the following formula for cells on myprintout sheet (image1) cells , the formula references the information inmy data sheet (image2),
- For cell A1 of Printout: =if(A4<>””,A1, IF(B4<>””,B1, IF(C4<>””,C1, IF(D4<>””,D1,IF(E4<>””,E1)))))
-For cell B1 of printout: =IF(B4<>””,B1,IF(C4<>””,C1, IF(D4<>””,D1, IF(E4<>””,E1))))
-For cell C1: = IF(C4<>””,C1, IF(D4<>””,D1,IF(E4<>””,E1)))
-Etc…
The formulas represent if any value, “1” in this case ispresent in the cell, then produces the corresponding title of the inspection onthe Printout sheet.
I did by deleting the previous cell/inspection in the nextcell in order to only list an inspection once for space saving and redundancyavoidance. However, I still won’t have enough space to only drop one inspectionfor the formula in the next Cell. Also,if there is a series of inspections that aren’t due, but one that is duefurther down the column line, I will get the same inspection repeating on theprintout sheet until it reaches a cell where the formula does not contain thatinspection (has move passed). (image3) shows my data, current results, and desired results.
Is there a way to construct a formula that will recognizewhen an inspection has been populated and then automatically move to the nextinspection?
IMAGE1:
IMAGE2:
IMAGE3:
I’m attempting to create a spreadsheet for maintenance atwork, but having problems getting what I’m looking for. I have a Data Input page where I have myvehicles in the rows and inspections & date of inspection in thecolumns. I manually input the dates forthe inspections. I’m trying to create aquick reference sheet that automatically shows the upcoming inspections. I figured out how to make excel show me whatis coming due within the next XX days. The issue is this, there won’t be enough space to have individual cellsdedicated to all my possible inspections on my reference print out. I want the sheet to list only the inspectionsthat are due in the next, say 10 days. Ihave, 40 possible inspections per vehicle, but I know that no more than 15 willcome due for the same vehicle at the same time.
I have the data input sheet, I also have a nearly identicalsheet with one row beneath the dates that looks at dates and puts a “1”directly below the corresponding date if it is within 10 days from TODAY. On my desired “quick reference print outsheet,” I used multiple “IF” functions to reference the row with the “1”’ init, and it would populate the inspection.
-I used the following formula for cells on myprintout sheet (image1) cells , the formula references the information inmy data sheet (image2),
- For cell A1 of Printout: =if(A4<>””,A1, IF(B4<>””,B1, IF(C4<>””,C1, IF(D4<>””,D1,IF(E4<>””,E1)))))
-For cell B1 of printout: =IF(B4<>””,B1,IF(C4<>””,C1, IF(D4<>””,D1, IF(E4<>””,E1))))
-For cell C1: = IF(C4<>””,C1, IF(D4<>””,D1,IF(E4<>””,E1)))
-Etc…
The formulas represent if any value, “1” in this case ispresent in the cell, then produces the corresponding title of the inspection onthe Printout sheet.
I did by deleting the previous cell/inspection in the nextcell in order to only list an inspection once for space saving and redundancyavoidance. However, I still won’t have enough space to only drop one inspectionfor the formula in the next Cell. Also,if there is a series of inspections that aren’t due, but one that is duefurther down the column line, I will get the same inspection repeating on theprintout sheet until it reaches a cell where the formula does not contain thatinspection (has move passed). (image3) shows my data, current results, and desired results.
Is there a way to construct a formula that will recognizewhen an inspection has been populated and then automatically move to the nextinspection?
IMAGE1:
IMAGE2:
IMAGE3:
Last edited:
