Solved

Increase a cell value by the date

Posted on 2013-02-01
12
213 Views
Last Modified: 2013-02-04
Guys, please see the attached and thank you for your help.

I have a sheet that imports hours and date to a spreadsheet similar to the attached. The imported info reflects the current total hours worked in the week.

After I import the file with the current weekly hours, I manually enter the current hours on the sheet that reflects the previous day that were worked. The formula does the math to give the result of the hours worked of the previous day. The imported info is a current and increasing weekly total from day to day.

Is there a code that would compare the dates then enter the respective daily hours on the cell that reflects the hours of the stipulated date?

Thank you, Robert
dates.xlsx
0
Comment
Question by:rws1
  • 5
  • 5
  • 2
12 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
=LOOKUP(1000000000000000,F5:F11)
0
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
If the imported number of hours that go into E3 increase every day, i.e. they represent the sum of all hours worked during the week no worksheet formula can save you from the job of transferring that number to the appropriate row in the range E5:E11 manually. This is because if one were to compare the dates, as you suggest, the row to enter the current number could be found but the previous day's number would be lost when the new day's number is written. A solution of this problem would require code.
However, one might look at your import procedure. Perhaps it is possible to write the number directly to the appropriate cell in E5:11. Very likely, the same argument would make it impossible. However, seeing that E3:F3 are quite superfluous, if you were to employ code you should employ it for the import of the data rather than their processing.
0
 

Author Comment

by:rws1
Comment Utility
Thank you for your response, what would be the code on the import sheet?
0
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
I don't know.
You wrote:-
I have a sheet that imports hours and date to a spreadsheet similar to the attached. The imported info reflects the current total hours worked in the week.
So, if your sheet "imports hours and date" you must refer to the data in E3:F3. How do you import them?
0
 

Author Comment

by:rws1
Comment Utility
Guys, thank you for your responses, I probably did a poor job of communicating my question, apologies.
I went a little direction, I put this code into a macro button and it is working for me.

Private Sub updt_Click()
Unload Me
If Range("I4") = Range("C7") Then Range("I7") = Range("D4")
If Range("I4") = Range("C8") Then Range("I8") = Range("D4")
If Range("I4") = Range("C9") Then Range("I9") = Range("D4")
If Range("I4") = Range("C10") Then Range("I10") = Range("D4")
If Range("I4") = Range("C11") Then Range("I11") = Range("D4")
If Range("I4") = Range("C12") Then Range("I12") = Range("D4")
If Range("I4") = Range("C13") Then Range("I13") = Range("D4")
End Sub

Should I reward the points to both who offered suggestions. Please let me know.
Thank you,
Robert
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
If the other comments did not help and yours is the only working solution then you should accept your own solution
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
Comment Utility
Normally, you would rather use the Select statement in place of so many Ifs. But in this case I would use a loop and an array like this:-
Dim i as Integer
Dim Arr() As Variant
Arr = Array(17, 18, 19, 110, 111, 112, 113)
For i = 7 To 13
    If Range("14").Value = Range("C" & i).Value Then Exit For
Next i
If i <= 13 Then
    Range(CStr(Arr(i -7)).Value = Range("D4").Value
End If

Open in new window

0
 

Author Comment

by:rws1
Comment Utility
Thank you guys
0
 

Author Comment

by:rws1
Comment Utility
Faustulus, I thought I had your formula working but it is producing an error. Would you mind to see it on sheet3?
Thank you.
dates.xlsm
0
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
Sorry about the problem!
I'll look at your workbook as soon as I get to my PC.
0
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
I wrote that code on an iPad. Indeed, I forgot to specify the column in the target range. I had also referred to Range("14") instead of Range("I4").The following code corrects the errors. It also refers to Range("E4") instead of Range("I4").
Private Sub Worksheet_Activate()

    Dim i As Integer
    Dim Arr() As Variant
    
    Arr = Array(17, 18, 19, 110, 111, 112, 113)
    For i = 7 To 13
        If Range("E4").Value = Range("C" & i).Value Then Exit For
    Next i
    If i <= 13 Then
       Range("I" & CStr(Arr(i - 7))).Value = Range("D4").Value
    End If
End Sub

Open in new window

The above code also suggests to use the Worksheet_Activate event Observe the procedure's declaration). The Selection_Change event you have been using occurs every time you click anywhere in teh worksheet. This may cause unexpected results simply because you tend to forget that the code is replacing the same total so many times each day. Choosing the Activate event will reduce the number of times the code is run, but perhaps it isn't suitable for your work flow.
The Worksheet_Change event isn't suitable because the change occurs in another sheet. However, if the change is made manually there you could capture the event on that sheet and let it run the same code you now have on this sheet - I hope you can follow. Perhaps there is a change you make daily somewhere in the workbook that you can harness as a trigger.
There are two alternatives. One is to limit the action to occur not on every click but only when a certain cell or range of cells is clicked. The other is to set a variable that prevents the action to be taken more than once.That variable would be lost when Excel is shut down, so that the procedure can run again the next day. It could also be reset by another even in the workbook. The point is that you want the procedure to run when it needs to run but stop it from repeatedly doing the same job.
0
 

Author Comment

by:rws1
Comment Utility
Thank you, Faustulus, you are right the activate method is better.

And it works great.

Robert
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now