Excel Auto Updates Stop Limit

%3CLINGO-SUB%20id%3D%22lingo-sub-1981928%22%20slang%3D%22en-US%22%3EExcel%20Auto%20Updates%20Stop%20Limit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1981928%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20sure%20there%20is%20already%20an%20answer%20out%20there%20on%20the%20forum%20for%20this%20but%20I%20am%20not%20sure%20what%20key%20words%20I%20am%20missing%20to%20find%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20a%20Stock%20Tracker%20that%20uses%20lives%20updates%20for%20my%20%22home%20page%22%20and%20I'm%20having%20problems%20transferring%20the%20data%20across%20sheets%20at%20a%20specific%20time%20each%20day.%26nbsp%3B%20For%20example%20%3DIF(TODAY()%3DDATEVALUE(%2212%2F11%2F2020%22)%2C%20Home!%24I%2410%2C0)%20is%20the%20current%20formula%20I%20have%20worked%20out%20but%20it%20is%20not%20quite%20doing%20what%20I%20want%20it%20to%20do%2C%20any%20date%20after%2012%2F11%2F2020%20it%20reverts%20to%200.%26nbsp%3B%20At%20the%20end%20of%20the%20trading%20day%20I%20want%20to%20find%20a%20way%20so%20that%20the%20cell%20to%20be%20updated%20will%20reference%20the%20day%20total%20and%20lock%20that%20value%20in%20to%20feed%20my%20line%20graph.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20there%20are%20probably%20dozens%20of%20these%20already%20made%20but%20I%20love%20this%20program%20and%20learning%20new%20things%20about%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1981928%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1981954%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Auto%20Updates%20Stop%20Limit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1981954%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F899565%22%20target%3D%22_blank%22%3E%40andrew165%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20this%20will%20solve%20the%20purpose.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20one%26nbsp%3B%3DIF(TODAY()%3DTODAY()%2C%22True%20or%20Insert%20text%20whatever%20you%20want%20to%20do%22%2C%220%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20same%20day%20it%20will%20show%20the%20value%20you%20want%20to%20show%20and%20from%20next%20day%20it%20will%20show%200%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20do%20let%20me%20know%20if%20it%20solved%20the%20purpose%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1981955%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Auto%20Updates%20Stop%20Limit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1981955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F894251%22%20target%3D%22_blank%22%3E%40AshishBatra%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirstly%2C%20thank%20you%20for%20your%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20you%20gave%20did%20not%20correct%20the%20issue%20I'm%20having%2C%20I%20will%20try%20to%20clarify.%26nbsp%3B%20I%20would%20like%20the%20Cell%20on%20the%20home%20page%20to%20feed%20the%20cell%20on%20sheet%202%20which%20is%20an%20easy%20thing%20to%20do%2C%20however%20I%20am%20trying%20to%20have%20the%20cell%20on%20sheet%202%20retain%20the%20value%20from%20the%20cell%20on%20the%20home%20page%20at%20a%20specific%20date%20and%20time.%26nbsp%3B%20For%20instance%2012%2F11%2F2020%204%20P.M%20EST%20(market%20close)%20and%20maintain%20that%20value%20while%20the%20cell%20on%20the%20home%20page%20is%20updating%20again%20the%20next%20day%20and%20feeding%20the%20next%20cell%20in%20line.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1983088%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Auto%20Updates%20Stop%20Limit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1983088%22%20slang%3D%22en-US%22%3E%3CP%3EI%20found%20the%20answer%2C%20it%20will%20be%20a%20VBA%20to%20calculate%20only%20once%20across%20the%20cell%20based%20on%20the%20variables.%26nbsp%3B%20though%20I'm%20not%20too%20sure%20how%20to%20it%20to%20edit%20it%20to%20meet%20my%20requirements.%26nbsp%3B%20Link%20is%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsuperuser.com%2Fquestions%2F450019%2Fhow-to-have-a-cell-calculate-only-once%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsuperuser.com%2Fquestions%2F450019%2Fhow-to-have-a-cell-calculate-only-once%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20many%20sheets%20in%20my%20workbook%2C%20the%20first%20being%20my%20dashboard%20which%20holds%20all%20of%20my%20calculations%20and%20my%20live%20updates%20and%20I'm%20using%20my%20second%20sheet%20%22December%202020%22%20to%20work%20out%20the%20kinks%20prior%20to%20the%20new%20year.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20cell%20'Dashboard!I10'%20I%20have%20my%20calculation%20running%20for%20my%20daily%20gain%20%25%20which%20updates%20based%20off%20the%20live%20inputs%20in%20other%20cells.%26nbsp%3B%20Cell%20'December%202020!B15'%20is%20the%20desired%20cell%20to%20be%20updated%20when%20the%20date%20and%20time%20parameters%20are%20met%20(i.e.%20December%2014th%202020%20at%204%3A00%20PM%20EST)%20and%20retain%20the%20value%20given%20at%20the%20end%20of%20the%20day.%26nbsp%3B%20While%20the%20next%20day%20cell%20'Dashboard!I10'%20continues%20to%20update%20but%20instead%20it%20reflects%20in%20cell%20'December%202020!B16'%20and%20so%20on.%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
New Contributor

I am sure there is already an answer out there on the forum for this but I am not sure what key words I am missing to find it.

 

I am working on a Stock Tracker that uses lives updates for my "home page" and I'm having problems transferring the data across sheets at a specific time each day.  For example =IF(TODAY()=DATEVALUE("12/11/2020"), Home!$I$10,0) is the current formula I have worked out but it is not quite doing what I want it to do, any date after 12/11/2020 it reverts to 0.  At the end of the trading day I want to find a way so that the cell to be updated will reference the day total and lock that value in to feed my line graph.

 

I know there are probably dozens of these already made but I love this program and learning new things about it.

3 Replies

Hi @andrew165 

 

Not sure this will solve the purpose. 

 

Try this one =IF(TODAY()=TODAY(),"True or Insert text whatever you want to do","0")

 

On the same day it will show the value you want to show and from next day it will show 0 

 

Please do let me know if it solved the purpose

@AshishBatra 

 

Firstly, thank you for your reply.

 

The formula you gave did not correct the issue I'm having, I will try to clarify.  I would like the Cell on the home page to feed the cell on sheet 2 which is an easy thing to do, however I am trying to have the cell on sheet 2 retain the value from the cell on the home page at a specific date and time.  For instance 12/11/2020 4 P.M EST (market close) and maintain that value while the cell on the home page is updating again the next day and feeding the next cell in line.

I found the answer, it will be a VBA to calculate only once across the cell based on the variables.  though I'm not too sure how to it to edit it to meet my requirements.  Link is below.

 

https://superuser.com/questions/450019/how-to-have-a-cell-calculate-only-once

 

I have many sheets in my workbook, the first being my dashboard which holds all of my calculations and my live updates and I'm using my second sheet "December 2020" to work out the kinks prior to the new year.

 

In cell 'Dashboard!I10' I have my calculation running for my daily gain % which updates based off the live inputs in other cells.  Cell 'December 2020!B15' is the desired cell to be updated when the date and time parameters are met (i.e. December 14th 2020 at 4:00 PM EST) and retain the value given at the end of the day.  While the next day cell 'Dashboard!I10' continues to update but instead it reflects in cell 'December 2020!B16' and so on.