Excel function for reminder

[Ramesh h&m]

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.

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Ramesh h&m]

Hi, i have attached excel chart for your kind reference

[JohnTopley]

Try

=IFERROR(INDEX(CONSTANT!$A$3:$A$49,SMALL(IF(CONSTANT!$B$3:$B$49=TODAY(),ROW(CONSTANT!$A$3:$A$49)-ROW($A$3)+1,""),COLUMNS($A:A))),"")

,...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Results in separate columns: why concatenate as it adds no "value" to the result

[BoredWorker]

Hi Ramesh h&m,

if there have space,
Did you mind that the list is listed down one by one?

as it will easier add in other column such as lookup to address, phone number, checklist and etc.

you can use the below array formula to do so.

Please Login or Register  to view this content.

PS: array formula is need to confirm by Ctrl + Shift + Enter instead of Enter only

If not understand, please follow below step:

1, Paste the formula to the cell
2, Double click on the cell
3, press Ctrl + Shift + Enter
It show { sign in front of formula. (ie {=index(.....)

[Ramesh h&m]

Noted thanks
Your formula is useful..

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.