SOLVED

Creating a time delayed answer

%3CLINGO-SUB%20id%3D%22lingo-sub-2827514%22%20slang%3D%22en-US%22%3ECreating%20a%20time%20delayed%20answer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2827514%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20...%3C%2FP%3E%3CP%3EThis%20may%20be%20wishful%20thinking%20but%20I%20would%20much%20appreciate%20being%20made%20aware%20of%20a%20solution%20if%20one%20exists.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20dictionary%20of%20Welsh%2FEnglish%20words%20and%20am%20using%20this%20to%20test%20myself%20as%20to%20the%20meaning%20of%20the%20former.%20For%20this%20purpose%20my%20spreadsheet%20has%20two%20adjacent%20columns%20as%20shown%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Iriwel_0-1633706921846.png%22%20style%3D%22width%3A%20476px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316226i34F18EFC3C31B482%2Fimage-dimensions%2F476x88%3Fv%3Dv2%22%20width%3D%22476%22%20height%3D%2288%22%20role%3D%22button%22%20title%3D%22Iriwel_0-1633706921846.png%22%20alt%3D%22Iriwel_0-1633706921846.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIn%20Column%20E%20the%20formula%20used%20is%26nbsp%3BINDEX(%24A%3A%24A%2CRANDBETWEEN(1%2CCOUNTA(%24A%3A%24A))%2C1)%20while%20the%20formula%20in%20Column%20G%20is%26nbsp%3BINDEX(%24C%246%3A%24C%24600%2CMATCH(%24E%242%2C%24A%246%3A%24A%24600%2C0)).%20%3CSTRONG%3EIs%20there%20a%20way%20in%20which%20the%20answer%20displayed%20in%20Column%20G%20can%20be%20delayed%20by%2C%20say%2C%2030%20seconds.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20appreciate%20any%20recommendations%20and%20thanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJames%20Clancy%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2827514%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2828172%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20time%20delayed%20answer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2828172%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1024398%22%20target%3D%22_blank%22%3E%40Iriwel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EHere%20is%20an%20approach%20to%20a%20solution%20with%20VBA.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EOption%20Explicit%0APrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%0A%20%20%20%20If%20Target.Address%20%3D%20%22%24G%242%22%20Then%0A%0A%20%20%20%20%20%20%20%20Application.OnTime%20Now%20%2B%20TimeValue(%2200%3A00%3A03%22)%2C%20%22Show%22%0A%0A%20%20%20%20End%20If%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20was%20able%20to%20help%20you%20with%20this%20info.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolinoDE%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWas%20the%20answer%20useful%3F%20Mark%20them%20as%20helpful!%3C%2FP%3E%3CP%3EThis%20will%20help%20all%20forum%20participants.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2829451%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20time%20delayed%20answer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2829451%22%20slang%3D%22en-US%22%3EHello%20...%3CBR%20%2F%3EMany%20thanks%20for%20your%20prompt%20reply%20to%20my%20query.%20I%20loaded%20the%20VBA%20(Alt%2FF11)%20with%20your%20solution%20but%2C%20unfortunately%2C%20it%20doesn't%20seem%20to%20be%20behaving%20as%20it%20should.%20Am%20I%20missing%20a%20trick%20somewhere%3F%3CBR%20%2F%3ERegards.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2829547%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20time%20delayed%20answer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2829547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1024398%22%20target%3D%22_blank%22%3E%40Iriwel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20example%20file%20with%20the%20code%20and%20how%20it%20is%20used.%3C%2FP%3E%3CP%3EIt's%20just%20a%20trick%20in%20which%20you%20can%20create%20a%20time%20delay.%3C%2FP%3E%3CP%3EIt%20could%20possibly%20also%20be%20displayed%20with%20a%20formula%20in%20which%20other%20calculations%20are%20carried%20out%20between%20input%20and%20output%20(in%20simple%20terms)%2C%20but%20the%20circular%20reference%20errors%2C%20as%20well%20as%20other%20possible%20errors%2C%20would%20prevent%20me%20from%20doing%20so.%20But%20that's%20just%20my%20humble%20opinion%3A)).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20any%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2829561%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20time%20delayed%20answer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2829561%22%20slang%3D%22en-US%22%3EGot%20it.%20Thank%20you%20so%20much%20for%20your%20help.%3CBR%20%2F%3ERegards%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2829570%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20time%20delayed%20answer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2829570%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1024398%22%20target%3D%22_blank%22%3E%40Iriwel%3C%2FA%3E%2C%20it%20may%20be%20late%20but%20here%20is%20my%20workaround%20without%20macros%20and%20with%20iterative%20calculation.%20Yes%2C%20with%20the%26nbsp%3Bannoying%20circular%20references%20that%20we%20dislike%20so%20much%20if%20they%20are%20not%20properly%20controlled.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PedroWave_0-1633775374619.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316310i352A706A475A6393%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22PedroWave_0-1633775374619.png%22%20alt%3D%22PedroWave_0-1633775374619.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20modify%20the%20number%20of%20seconds%20in%20cell%20I2.%3C%2FP%3E%3CP%3EYou%20need%20to%20repeatedly%20press%20the%20F9%20key%20to%20iteratively%20calculate%20the%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EPedro%20Wave%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello ...

This may be wishful thinking but I would much appreciate being made aware of a solution if one exists.

 

I have created a dictionary of Welsh/English words and am using this to test myself as to the meaning of the former. For this purpose my spreadsheet has two adjacent columns as shown below.

 

Iriwel_0-1633706921846.png

In Column E the formula used is INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1) while the formula in Column G is INDEX($C$6:$C$600,MATCH($E$2,$A$6:$A$600,0)). Is there a way in which the answer displayed in Column G can be delayed by, say, 30 seconds.

 

Would appreciate any recommendations and thanks in advance.

 

Regards

 

James Clancy

 

 

 

6 Replies

@Iriwel 

Here is an approach to a solution with VBA.

 

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$G$2" Then

        Application.OnTime Now + TimeValue("00:00:03"), "Show"

    End If

End Sub

 

 

Hope I was able to help you with this info.

 

NikolinoDE

 

Was the answer useful? Mark them as helpful!

This will help all forum participants.

 

Hello ...
Many thanks for your prompt reply to my query. I loaded the VBA (Alt/F11) with your solution but, unfortunately, it doesn't seem to be behaving as it should. Am I missing a trick somewhere?
Regards.
best response confirmed by Iriwel (Occasional Contributor)
Solution

@Iriwel 

Here is the example file with the code and how it is used.

It's just a trick in which you can create a time delay.

It could possibly also be displayed with a formula in which other calculations are carried out between input and output (in simple terms), but the circular reference errors, as well as other possible errors, would prevent me from doing so. But that's just my humble opinion:)).

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

Got it. Thank you so much for your help.
Regards

@Iriwel, it may be late but here is my workaround without macros and with iterative calculation. Yes, with the annoying circular references that we dislike so much if they are not properly controlled.

 

PedroWave_0-1633775374619.png

 

You can modify the number of seconds in cell I2.

You need to repeatedly press the F9 key to iteratively calculate the formulas.

 

Regards,

Pedro Wave

Pedro ...
Many thanks for this very helpful solution. I'll try it out over the next few days alongside an earlier suggestion from NicolinoDE.

Also, thanks for the inclusion of additional vocabulary - that really impressed me.

Regards.