Formula for auto updating calendar

So I am creating a spreadsheet that has automatically updating calendars for testing schedule. I've gotten pretty far with it but now after thinking about it want to go one step further. I used this formula to simply populate the planned testing date into its proper location on the calendar and it worked beautifully.

{=IFERROR(INDEX('Calendar Data'!$C$9:$C$301,SMALL(IF($A$5='Calendar Data'!$A$9:$A$301,ROW('Calendar Data'!$A$9:$A$301)-MIN(ROW('Calendar Data'!$A$9:$A$301))+1,""),ROW(A1)))," ")}

Now I want to have it so that if its only planned it will populate under the proper date, but if theres an actual test date entered, that it will "move" to the day it actually went through testing. This is the formula I have so far but its not working properly.

{=IFERROR(IF(ISNUMBER(SEARCH("TBD",'Calendar Data'!$B$9:$B$301)),INDEX('Calendar Data'!$C$9:$C$301,SMALL(IF($B$93='Calendar Data'!$A$9:$A$301,ROW('Calendar Data'!$A$9:$A$301)-MIN(ROW('Calendar Data'!$A$9:$A$301))+1,""),ROW(B1))),INDEX('Calendar Data'!$D$9:$D$301,SMALL(IF($B$93='Calendar Data'!$B$9:$B$301,ROW('Calendar Data'!$B$9:$B$301)-MIN(ROW('Calendar Data'!$B$9:$B$301))+1,""),ROW(B1)))),"")}

I've attatched the file that I am working with. Testing the formulas on the Janurary tab, the 29th -31st because i have four tests from the calendar data tab that have all the same dates, except one ive changed the actual to TBD so I can see if the formula is working the way I want it to. The first test should show up in the 29th due to the TBD and the other three should show up in the 31st due to having actual test dates entered.

Thanks!!

You are a god thank you so much

Originally Posted by Bernie Deitrick

In cell B95, array enter (enter using Ctrl-Shift-Enter) the formula

=IFERROR(INDEX('Calendar Data'!$C:$C,SMALL(IF('Calendar Data'!$B$9:$B$300="TBD",IF('Calendar Data'!$A$9:$A$300=B$93,ROW($B$9:$B$300))),ROW(A1))),IFERROR(INDEX('Calendar Data'!$D:$D,SMALL(IF('Calendar Data'!$B$9:$B$300=B$93,ROW($B$9:$B$300)),ROW(A1)-COUNTIFS('Calendar Data'!$B$9:$B$300,"TBD",'Calendar Data'!$A$9:$A$300,B$93))),""))

and copy down, then across.

Note that when you copy to other blocks of dates, you will need to change the $93 to the row with the date for that block before you copy the formula down and across.

Now is there anyway to carry over the red formatting for the TBD dates to the calendar?

Originally Posted by Bernie Deitrick

If your entries in column C of Calendar Data are unique, you could use CF with the formula option and the formula

=NOT(ISERROR(MATCH(B95,'Calendar Data'!$C:$C,False)))

Depending on your version of Excel, you may need to name the range in 'Calendar Data'!$C:$C to make that CF formula work (I forget if and when using ranges off-sheet in CF was changed...)

You are seriously amazing. Thank you sooooooo much