Forum Discussion
Excel using cell color to select data from another spreadsheet
I have a calendar built in excel and are using conditional formating to change the color of a cell using a separate spreadsheet to determine which cells to color. The calendar worksheet is calcul...
Shotgunenator Without seeing the file it a bit of a guess how that formula should look like but you could build it as follows, where the words between <> refer to the relevant ranges that contain what's described.
=INDEX(<birthday_names>, MATCH(<calendar_date>, <birthday_dates>, 0 )
And if you give these ranges meaningful names you can avoid including sheet names and direct/absolute references in the formulae. It will make them easier to write, read and maintain.
NOTE: This is only pick-up one name per date. If you have people sharing a birthday, consider putting their names in the same cell. Otherwise it becomes much more complicated, unless you are on MS365 or Excel 2021. Then you may the FILTER function.
I was able to use this formula to replicate my desired results in another location on the workbook, but was not able to use the formula in conjunction with the original formula.
Original =IF(MONTH(DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1)+(ROW()-5)*7+COLUMN()-3-WEEKDAY(DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1),2))=MATCH($E$3,'Month Names'!$A$1:$A$12,0),DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1)+(ROW()-5)*7+COLUMN()-3-WEEKDAY(DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1),2),"")
Success with index and match =IFERROR(INDEX(Names2,MATCH(VALUE(F5),Dates2,0)),"")
Names2 and Dates2 are refrences to the columns that the data is included
Original =IF(MONTH(DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1)+(ROW()-5)*7+COLUMN()-3-WEEKDAY(DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1),2))=MATCH($E$3,'Month Names'!$A$1:$A$12,0),DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1)+(ROW()-5)*7+COLUMN()-3-WEEKDAY(DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1),2),"")
Success with index and match =IFERROR(INDEX(Names2,MATCH(VALUE(F5),Dates2,0)),"")
Names2 and Dates2 are refrences to the columns that the data is included