Solved

Increase a cell value by the date

Posted on 2013-02-01
12
226 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
ID: 38846072
=LOOKUP(1000000000000000,F5:F11)
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 38846094
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
ID: 38846539
Thank you for your response, what would be the code on the import sheet?
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 38847830
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
ID: 38848924
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
ID: 38849061
If the other comments did not help and yours is the only working solution then you should accept your own solution
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
ID: 38849161
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
ID: 38849421
Thank you guys
0
 

Author Comment

by:rws1
ID: 38849484
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
ID: 38852368
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
ID: 38853395
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
ID: 38853555
Thank you, Faustulus, you are right the activate method is better.

And it works great.

Robert
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

22 Experts available now in Live!

Get 1:1 Help Now