?
Solved

Daily basis, refresh information from "B" to "I" column.

Posted on 2016-09-29
31
Medium Priority
?
98 Views
Last Modified: 2016-10-01
I am trying to Have a code on the main page that will on a daily basis be automatically refresh information on the "I" column from information being provided by "B" column

For a visual description please check the provided link. Thanks again guys for your time and your help.

http://www.screencast.com/users/Omarantonio1231/folders/Refresh%20Dates/media/f19fa1e9-3805-40f9-8bb7-ced979e14608
0
Comment
Question by:Omar Hernandez
[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
  • 15
  • 9
  • 6
31 Comments
 

Author Comment

by:Omar Hernandez
ID: 41822939
I have updated the video to make it more simple please see video description on the following link.

http://www.screencast.com/users/Omarantonio1231/folders/Refresh%20Dates/media/7a3b0223-cff4-40ea-be93-594d56fa0ab7
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41822998
Hi Omar,

Aikimark provided you a SOLUTION HERE which inputs the difference between today's date and the date in col. B automatically in column I once you select a date from the calendar in col. B.
Isn't it working as expected or what else you are trying to achieve?
Would be better if you just give some sample dates in col. B and your desired output in col. I.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41823271
This is a different question than the one you asked after you accepted my comment as the solution in the prior question.

What is wrong with putting a formula in that column to display the day difference?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Omar Hernandez
ID: 41823566
Neeraj and aikimark the solution for the other question is on point, but if the days pass on to the next day the actual file stays the same. What i want this file/code to do is "I" and "J" will update its information when the actual date moves closer to the date input from column "B".

FOR EXAMPLE

*** Column ("B" if day is 10/7)  = ("I" #7) and ("J" being greater than 5 days will be only 5 Q)

From actual date moving from 9/30 now it moved to 10/1

***Column should now be on ("I" #6) and ("J" being greater than 5 days will be only Q)

From actual date moving from 10/1 now it moved to 10/2

***Column should now be on ("I" #5) and ("J" being greater or equal to 5 days will be only 5 Q)

From actual date moving from 10/2 now it moved to 10/3

***Column should now be on ("I" #4) and ("J" being less than 5 days will look for the number and display the same number of Q = to I, this will be = to (4 Q)
0
 

Author Comment

by:Omar Hernandez
ID: 41823624
Another video demonstration showing what is the inconvenience i am having. Please check the new video link. Thanks for your help guys and your time...

http://www.screencast.com/users/Omarantonio1231/folders/Refresh%20Dates/media/613eb4a3-9205-4f21-a731-59363bfc225c
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41823748
That is what formulas are for. they can refresh, based on conditions and are usually calculated when the workbook opens.  The number of "Q" characters will be a result of the REPEAT() function.
0
 

Author Comment

by:Omar Hernandez
ID: 41823761
Hey akimark, when i save the file close the excel file, close it change the date and then open it again it does not refresh the information on the "I" and "J" column
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41823978
Because you insisted that the VBA code place values into the two columns.  I tried to sway you to place formulas there.
0
 

Author Comment

by:Omar Hernandez
ID: 41824024
I understand your point aikimark, Hey friend if there is no way of getting it to work the way i intentionally wanted. I would understand if you told me that it cannot be possible...

Ok. now that i understand that it cannot be possible may we try a different approach, as the one that you have suggested, would deeply appreciate it akimark.
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41824070
Hi Omar,

In the CODE suggested by Aikimark, change the line#27
Cells(Target.Row, 9).Value = eval

Open in new window


WITH THIS
Cells(Target.Row, 9).Formula = "=B" & Target.Row & "-Today()"

Open in new window

See if that resolves your issue.
0
 

Author Comment

by:Omar Hernandez
ID: 41824083
Hello there Neeraj, replaced what you have suggested no luck there friend. if it does help i will be attaching the file on this message. Hope this will make it much easier for you guys.
BK-TO-DO-LIST.xlsm
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41824105
Please refer to the attached and see if that works.
BK-TO-DO-LIST-1.xlsm
0
 

Author Comment

by:Omar Hernandez
ID: 41824114
I am very glad that one column is working how suggested, Now neeraj on column "J" i would also want it to also update it's information as well.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41824192
Formula for I34:
=IF(isvaliddate(B34),MAX(B34-TODAY(),0),"")

Open in new window

The formula for J34 will be something like this:
=IF(I34="","",REPT("Q",MIN(5,I34)))

Open in new window

The IsValidDate function I added:
Function IsValidDate(ByVal parmValue) As Boolean
    IsValidDate = IsDate(parmValue)
End Function

Open in new window

0
 

Author Comment

by:Omar Hernandez
ID: 41824210
where should i add ( The IsValidDate function I added:), part
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 1000 total points
ID: 41824221
In one of your modules
0
 

Author Comment

by:Omar Hernandez
ID: 41824246
Hey Akimark i have done the update you have suggested but on column "J" nothing moves. should i erase the following information or keep it

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim eval As Long
    Dim colors As Variant
    colors = Array(0, -16776961, 26367, 26367, 26367, -65536)
    If Target.Column = 2 Then
        If Cells(Target.Row, 2).Value = Empty Then
            Cells(Target.Row, 10).Value = ""
            Exit Sub
        End If
        If IsDate(Cells(Target.Row, 2).Value) Then
            eval = Cells(Target.Row, 2).Value - Date
            Select Case eval
                Case 0
                    Cells(Target.Row, 10).Value = ""
                Case 1 To 4
                    Cells(Target.Row, 10).Value = String(eval, "Q")
                    Cells(Target.Row, 10).Font.Color = colors(eval)
                Case Is > 4
                    Cells(Target.Row, 10).Value = String(5, "Q")
                    Cells(Target.Row, 10).Font.Color = colors(5)
            End Select
            With Cells(Target.Row, 10).Font
                .name = "Snap ITC"
                .FontStyle = "Bold"
                .Size = 8
            End With
            Cells(Target.Row, 9).Formula = "=B" & Target.Row & "-Today()"

        End If
    End If

End Sub

Open in new window

0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41824451
@aikimark
I assume no function is required here to validate a date entry in column B as dates are inserted into column B with calendar pop up triggered by selection event.
0
 

Author Comment

by:Omar Hernandez
ID: 41824455
Hey guys, so what is your conclusion is there a way we can get it to work, with those two column being updated from "B" column
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41824457
Please refer to the attached.
BK-TO-DO-LIST-1.xlsm
0
 

Author Comment

by:Omar Hernandez
ID: 41824463
Hey there Neeraj, really appreciate the update works like a charm. Before i close this question that has been answer, on the Column that has the letter Q displayed. Instead of the Q being centered.

***May you code it so that on the column that shows the Q it  be align on the Left side. In advance thank you guys for your help.
0
 
LVL 32

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 1000 total points
ID: 41824504
Hi Omar,
Please refer to the attached.
BK-TO-DO-LIST-1.xlsm
0
 

Author Comment

by:Omar Hernandez
ID: 41824514
Thanks again neeraj, Very grateful for your time and help, as well to Akimark
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41824515
You're welcome Omar!
You should have split the points considering the time devoted and helpful assistance by the Aikimark as well.
Please use Request Attention button to reopen the question and split the points.
0
 

Author Comment

by:Omar Hernandez
ID: 41824518
sorry for that had no idea there was an option for that, where can i find this option (request attention)
0
 

Author Comment

by:Omar Hernandez
ID: 41824519
Hey there guys, i have submitted request attention and i sincerely apologize Akimark for the miss understanding on my part. You guys are awesome, thank's again.
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41824520
Thanks for using Request Attention Omar! :)
0
 

Author Closing Comment

by:Omar Hernandez
ID: 41824920
Thanks to both of you guys, your assistance has been appreciated 1000%
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41824922
You're welcome Omar! Glad we could help.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41824924
glad we could help
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.

741 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