• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Keep cell from updating , when other cells are cleared

R

rjwalters

New Member
Lets use A1,B1,C1,D1. C1 is the sum of A1 and B1. I want to be able to retain the SUM in D1 even after I clear the other cells.


What I am doing is tracking time, and at the end of the week they turn their time in, clear the sheet and start a new week. I want a way to carry the total time over to the following week so I can track the time on an entire project without manually adding it up.


I really dont have a clue where to start on this one.
 
Hi rjwalters,


According to my understanding, you want to capture the time (entered by other folks)at the end of the week.


If my understanding is correct then below here is my assumptions:


1)Monday to Friday are the working days


2)On every Friday, you need to capture the total time entered in that week and store the same somewhere in the workbook


3)Along with the total time, you also may need to store the date for your future reference


Based on the above assumptions, I have created a sample workbook of which the sheets are as follows:


http://speedy.sh/PBEbn/Time-Sheet.xlsm


A) Input Sheet:Where time is entered from B1 to F1


B) Database:


i) A1 contains the formula =TODAY() to get today's date

ii)B1 contains the below formula to know which day it is, based on today's date:


=CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")


Note: Where A1 is the date cell and 1 is the return-type (with 1 being Sunday through 7 being Saturday)


iii)Col G1 to I1 contains the headers where the values (date, day and total time) are required to be stored.


C)TimeCapture: Here we have the button (Update Time) where macro is assigned.Please check module1 (press alt+F11 from keyboard) for the same.


The logic of the code is:


IF today is 'FRIDAY' THEN capture today's DATE and DAY (from Database sheet) along with the total time entered(from Input Sheet) in the DATABASE sheet; give a message to the user, ELSE do nothing and give the proper instruction to the user.


**Since I have used TODAY function in the excel sheet, I have mentioned TUESDAY(As the code I have written today;Tuesday, January 5) in the IF logic of the code which you need to change to FRIDAY based on the logic that we are following.


Note:If you are looking at my post today(January 5, 2013), macro will do the copy paste work because, although it is Tuesday of the week it will copy paste the value as described above.


My questions / Thoughts:


However, I have shown you a very basic approach that you could follow, but I believe there would be different 'total time' based on each person and different projects.IF this is true, then please share a sample workbook so that we could provide you more customized solution.


Hope it helps a bit.


Best regards,

Kaushik
 
You must log in or register to reply here.
Back
Top