[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Increase a cell value by the date

Posted on 2013-02-01
12
Medium Priority
?
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
LVL 14

Accepted Solution

by:
Faustulus earned 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

656 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