I'm not quite sure if I'm using the right formula to be doing what I'm trying to do, but let me walk you through what I'm looking for.
I'm trying to put together a dashboard that facility managers for my company throughout the US can use as a one stop for all of the compliance documents that they need to access and can see their progress in terms of documents turned in. I have a column for documents(this is a drop down list that they can select different documents from to view the due dates, status, and missing documents from), due dates, status, and missing documents. So, when they select for instance "Monthly Facility Checklists", it will show them the due dates, status, and missing documents for that specific category of documents. Meaning that all of the information that is tethered to that category needs to change with each category when changed. I've managed to get the due dates to change relatively easily but I'm stuck with the due dates and status columns because I'm trying to provide color coding for the due dates that give specific colors once a certain amount of time is left to complete the document. The only trouble is that I need to link it to the status column so when they get it completed(an X in the column) it will take off the color for the box. This is also linked to another workbook that gets updated to show the dates these things are uploaded. Below is the formula I am currently using:
=IF(H6="X","",IF(H6='[F-17 EHS Compliance Calendar - 1.1.xlsx]KKUS & VT Reuse'!$D$36,"X",IF(A1=A28,H6,IF(E6='[F-17 EHS Compliance Calendar - 1.1.xlsx]KKUS & VT Reuse'!$D$35,"X",IF(G6="X","",IF(A4=A24,G6,IF(A4=A26,"X",IF(ISNUMBER(SEARCH(E6,'C:\Users\BLANK\Downloads\KKUS\Files\Documents and Forms\[F-17 EHS Compliance Calendar - 1.1.xls]BLANK & BLANK'!$E$35)),"X",""))))))))
I'm trying to force the X to change per category from the drop down based upon the completion in the other workbook. The problem is that the formula will start to ignore if certain cells are blank, which I don't want it to do, and then it will just use an "X" from elsewhere thereby justifying all of the other cells and making it look as though the document has been turned in even if it has not been.
Not sure if this has been easy to understand, I know it's a lot. I'm just curious if the IF/THEN statements are my best option of if there is something else that I should use instead.
I'm trying to put together a dashboard that facility managers for my company throughout the US can use as a one stop for all of the compliance documents that they need to access and can see their progress in terms of documents turned in. I have a column for documents(this is a drop down list that they can select different documents from to view the due dates, status, and missing documents from), due dates, status, and missing documents. So, when they select for instance "Monthly Facility Checklists", it will show them the due dates, status, and missing documents for that specific category of documents. Meaning that all of the information that is tethered to that category needs to change with each category when changed. I've managed to get the due dates to change relatively easily but I'm stuck with the due dates and status columns because I'm trying to provide color coding for the due dates that give specific colors once a certain amount of time is left to complete the document. The only trouble is that I need to link it to the status column so when they get it completed(an X in the column) it will take off the color for the box. This is also linked to another workbook that gets updated to show the dates these things are uploaded. Below is the formula I am currently using:
=IF(H6="X","",IF(H6='[F-17 EHS Compliance Calendar - 1.1.xlsx]KKUS & VT Reuse'!$D$36,"X",IF(A1=A28,H6,IF(E6='[F-17 EHS Compliance Calendar - 1.1.xlsx]KKUS & VT Reuse'!$D$35,"X",IF(G6="X","",IF(A4=A24,G6,IF(A4=A26,"X",IF(ISNUMBER(SEARCH(E6,'C:\Users\BLANK\Downloads\KKUS\Files\Documents and Forms\[F-17 EHS Compliance Calendar - 1.1.xls]BLANK & BLANK'!$E$35)),"X",""))))))))
I'm trying to force the X to change per category from the drop down based upon the completion in the other workbook. The problem is that the formula will start to ignore if certain cells are blank, which I don't want it to do, and then it will just use an "X" from elsewhere thereby justifying all of the other cells and making it look as though the document has been turned in even if it has not been.
Not sure if this has been easy to understand, I know it's a lot. I'm just curious if the IF/THEN statements are my best option of if there is something else that I should use instead.
