If/Then Statement Help

Pikedog55

New Member
Joined
Jul 13, 2018
Messages
1
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

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
Back
Top