"Progressive" IF function Question

Tyson2

New Member
Joined
Mar 1, 2014
Messages
2
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:

 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
have you considered to use conditional formatting?

e.g. to highlight upcoming maintenance in 2 weeks with CF set as cell value greater than or equal to today()-14
 

Forum statistics

Threads
1,137,125
Messages
5,679,769
Members
419,855
Latest member
Eddier32

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top