In cell G10, I have the =now() formula. Anytime the date changes from that =now(

Question

In cell G10, I have the =now() formula.

Anytime the date changes from that =now() formula to the next day, I would like another cell to change (E44) to the next set of text located in a range on another worksheet ('Commitments'!A3:A17) - in sequential order, and then repeat as the days cycle through. There are 15 lines of text to be displayed, and then continuously repeated.

So example:

G10 = Sept. 22nd

E44 = the text "this place is cool"

When G10 changes to = Sept. 23rd, I'd like E44 to change to the next line of text in the range, "Life is to be lived."

Range = 'Commitments'!A3:A17) =

A3 = "this place is cool"

A4 = "Life is to be lived."

A5 = "Insert text here"

All the way to A:17

I am at a loss if there is an existing formula to do this or if I need to set up a Macro, at which I'm also lost. I am open to completely changing any piece of the puzzle as long as the next text in sequential order shows up when the date changes - either manually or by "now()".

Explanation / Answer

Firstly, since you want a change to happen on Date and not on Time, I'd advise you to use "Today" instead of Now(). The below solution would work in both cases though.

Identify a cell (you may hide it later), say I10 and put formula =MOD(G10,15)+1 (OR =ROUNDDOWN(MOD(G10,15),0)+1) in case you use NOW() ).

Now, whenever the date changes, this would given you a changing number in sequential order and would change between 1 to 15.

Now in the cell E44 you can place formula =INDEX(Commitments,$I$10,1).

Hope this given you the desired results.

If this response answers your question then please mark it as an Answer.

Regards,13

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.