Highlight birthday in a referenced cell

0

in Excel 2003 in worksheet1, I have a conditional formatting that highlights a cell when today is the birthday

in cell A1 date of Birth

in cell B1 age with the formula:
=DATEDIF($A1,TODAY(),"Y") & " Years, " & DATEDIF($A1,TODAY(),"YM") & " Months, " & DATEDIF ($A1,TODAY(),"MD") & " Days"

with the conditional formatting:
=TEXT($A1,"DD/MM")-TEXT(TODAY(),"DD/MM")=0

which works perfectly

but in worksheet2, I have in cell B1 the formula
=worksheet1!B1

with the same conditional formatting which does not work

How can I make cell B1 in worksheet2 be highlighted when today is the birthday?

Hany

Posted 2016-02-07T07:04:28.110

Reputation: 207

Can you post a screenshot of your conditional formatting for Sheet2? What you've described should work fine. – Jonno – 2016-02-07T07:41:00.010

That conditional formatting formula cannot be right. B1 contains text, not a date, so the formula will return an error. It should refer to A1 instead of B1. Please check and update your question. – teylyn – 2016-02-08T01:21:22.820

I am sorry, you are right, the formula in B1 is =TEXT($A1,"DD/MM")-TEXT(TODAY(),"DD/MM")=0 But this works only in sheet1, not sheet2 – Hany – 2016-02-08T05:36:22.370

After reviewing teylyn comment, I found the solution, in cell A1 in worksheet2, I also referred to cell A1 of worksheet1; and this solved the problem. Thank you. – Hany – 2016-02-08T05:40:59.650

Answers

0

After reviewing the comment of teylyn, I found the solution, in cell A1 in worksheet2, I also referred to cell A1 of worksheet1; and this solved the problem. Thank you.

Hany

Posted 2016-02-07T07:04:28.110

Reputation: 207

1

You can add a sheet reference to your conditional formatting formula on sheet 2 to refer to the source cell on sheet 1.

=TEXT(Sheet1!$A1,"DD/MM")-TEXT(TODAY(),"DD/MM")=0

SincereApathy

Posted 2016-02-07T07:04:28.110

Reputation: 111

Not in 2003, you can't. CF in 2003 can only use references on the same sheet or in named ranges. – teylyn – 2016-02-08T01:15:11.743

you are right excel 2003 does not support referenced cells to other worksheet – Hany – 2016-02-08T05:22:08.340

1

in cell A1 date of Birth
in cell B1 age with the formula

with the conditional formatting:
=TEXT($B1,"DD/MM")-TEXT(TODAY(),"DD/MM")=0

Having age in B1, are you sure your conditional formula works? Isn't it referring to A1??

As sheet2 contains age, not date of birth, use this formula:
=FIND(B1," 0 Months, 0 Days")>0

Máté Juhász

Posted 2016-02-07T07:04:28.110

Reputation: 16 807

unfortunately it highlights all the cells. I also tried =FIND(B1," 0 Months 0 Days")=0, but it does not work – Hany – 2016-02-08T05:21:15.103

It's strange. Please post screenshots both on your data and the rules ( also from first Sheet) – Máté Juhász – 2016-02-08T06:57:12.633

– Hany – 2016-02-09T05:11:27.750

I've updated my formula, please check now – Máté Juhász – 2016-02-09T07:38:52.803

I tried your formula =FIND(B1," 0 Months, 0 Days")>0 and also I tried =FIND(B1," 0 Months, 0 Days")=0 but they both did not work – Hany – 2016-02-09T08:07:04.183

Asked: 2016-02-07T07:04:28.110

Viewed: 305 times

Active: 2016-03-01T08:34:50.127