Hi,
I have below chart in SHEET 1 (Sheet 1 name : "CONSTANT")
A B
1 EXPENSE REMINDER DATE
2 CAR LOAN 05.06.2017
3 GOLD LOAN 21.06.2017
4 HOUSING LOAN 21.06.2017
5 GROCERY 21.06.2017
6 EB 08.06.2017
7 WATER 21.06.2017
I Need auto generated reminder in SHEET 2 as,
A
1 TODAY GOLD LOAN & HOUSING LOAN & GROCERY & WATER
I used below excel functions,
SHEET 1 (Sheet 1 name : "CONSTANT")
C
1 =IF(F3=TODAY(),1,"")
2 =IF(IF(F4=TODAY(),1,0)=1,COUNT($J$3:J3)+1,"")
3 ''
4 ''
5 ''
6 ''
7 ''
And then,
SHEET 2
A
1 =CONCATENATE( "TODAY ",LOOKUP(1,CONSTANT!J3:J49,CONSTANT!A3:A49)," & ",LOOKUP(2,CONSTANT!J3:J49,CONSTANT!A3:A49)," & ",LOOKUP(3,CONSTANT!J3:J49,CONSTANT!A3:A49)," & ",LOOKUP(4,CONSTANT!J3:J49,CONSTANT!A3:A49)," & ",LOOKUP(5,CONSTANT!J3:J49,CONSTANT!A3:A49))
I am getting 2 issues,
1. If the REMINDER DATE is blank, the shee2 cell A1 returns "TODAY & & & &"
2. If the REMINDER DATE cell B7 is only occupied with today date, the sheet 2 cell A1 returns "TODAY WATER & WATER & WATER & WATER & WATER"
Kindly help to do the needful.
Bookmarks