Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

On cell value change, lookup header & copy to 2nd range (GBP £30 for commented code)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • On cell value change, lookup header & copy to 2nd range (GBP £30 for commented code)

    Hi Excel experts,

    I have a 4-day (96-hour) deadline on this, but really hope that someone out there might have a bit of time to spare?

    10% of the fee has already been paid to Ozgrid.

    Basically this is a site roster/staffing project with staffing columns that need to be updated to match any new entries made in the site columns. However, it also needs to be able to delete the appropriate columns if a user changes their mind (or is just trying out different staffing patterns) – and in my hands at least this is where a worksheet change event didn’t seem to be enough.

    Below are some notes about the attached file:

    Following the 2017 dates, which are in column B (with components split out into columns A and C), there are 3 ranges that for description purposes I’ll call LOCATION, SITE and STAFF. The sizes of these ranges are fixed at the moment, but it might be necessary to add additional columns to them in the future (more locations, more sites, more staff). There will always be a blank column between the SITE and the STAFF ranges (the blank column currently being column S).

    LOCATION range: There are pairs of columns with site locations (city, area etc) – there is no data in these right now as this will be pulled through from other workbooks (probably via an ‘update’ button). It’s not part of the project spec, but I’m mentioning it just in case it has an impact.

    STAFF range (columns T to Y): This holds data on staff availability and is where pre-booked holidays, meetings, site maintenance tasks, time reclaimed etc. will be entered here. This data does not need to be copied to any other range on the sheet.
    In the hope of making it simpler to distinguish between entries originating from this range and those originating from the SITE range (see later for reason why), any site maintenance tasks entered in the STAFF range will begin with a string that is not a Site title i.e. entered as “Maintenance: Site 1” not “Site 1 Maintenance”. Users should be able to enter and delete text freely from this range.

    SITE range (columns N to R): When a member of staff is allocated to a site their name will be entered under the appropriate Site Name (column header) and Date (row).
    • Excel should then check whether that member of staff is available (i.e. whether the corresponding cell in the STAFF range is empty)
      [*=1]If the staff member is available the entry should be allowed and the site name should be copied to that staff members column in the STAFF range
      [*=1]If they aren’t available the user should be notified of this via a message box, and the entry should not be allowed.
      [*=1]If the staff members name cannot be found in the STAFF range the user should be notified via a message box, and the entry should not be allowed.



    DELETING/AMENDING ENTRIES IN EITHER THE STAFF OR THE SITE RANGE:
    This is where it gets trickier. I originally tried to code the above using a very basic worksheet change/lookup combination see attached file, but then realised that if someone later changed their mind and deleted an entry, the system failed. I’ve seen posts elsewhere suggesting a hidden ‘mirror’ sheet to hold the pre-deletion values, but in the time that I have, that level of coding is way beyond me.

    • Any deletions made in the SITE range should lead to the deletion of the corresponding entry in the STAFF range
    • Any deletions made in the STAFF range should lead to the deletion of any corresponding entry in the SITE range, if one exists (N.B. there should only be a corresponding entry if the text begins with a Site Name)


    CHECK STATUS BEFORE SAVE?
    I’m not sure how feasible this final request is (and I can manage without it if necessary), or whether there’s a better alternative, but it would really help if there was a way to double-check that all entries in the SITE range have a corresponding entry in the STAFF range before saving , potentially catching any previous incidents where a user has forgotten to turn on macros?
    The STAFF and SITE columns will eventually pull through to separate workbooks where any mismatches between the two ranges probably won’t be apparent until it’s too late.

    ADDITIONAL NOTES:

    N.B. The staff names given are not real and neither are the site titles, so the code would probably need to refer to the header locations rather than specific strings.

    All cell fill/colouring can be ignored – as it will applied via conditional formatting later (I just used it to try to make the sample data/logic a bit easier to visualise).

    I think that’s it, but if I’ve missed anything please let me know.

    Thank you.

    Isame
    Attached Files
    Last edited by isame; December 28th, 2016, 10:01. Reason: old code removed

  • #2
    Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

    I can look at this for you. Will come back if I have any questions.
    Kris

    ExcelFox

    Comment


    • #3
      Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

      Thanks, that would be great.

      Comment


      • #4
        Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

        Question, will there be last name along with first name ?
        Kris

        ExcelFox

        Comment


        • #5
          Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

          Hi, that's a very good question. At the moment we I don't have to use a last name (as there are no two people with the same first name) BUT I'm more than happy to use a last name and I think it would make the macro more robust in the longer-term, so yes please code to use both a first name and last name.

          I should have thought of that, well spotted :-)

          Best wishes
          Isame

          Comment


          • #6
            Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

            The file is ready. Please make the payment(See PM for PayPal details) and I'll attach the workbook upon receipt of the payment.
            Kris

            ExcelFox

            Comment


            • #7
              Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

              Hi Kris,

              Payment made: transaction ID 7HR06697YV572500M
              Many thanks for working on this.

              Best wishes,
              Isame

              Comment


              • #8
                Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

                Got the payment and thanks. Please find attached file. Please test it and let me know if you need anymore help. If the user forgot to enable the macro, there is no mechanism which will ensure that everything is okay, unfortunately.
                Attached Files
                Kris

                ExcelFox

                Comment


                • #9
                  Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

                  Hi, I'm still testing it, and in general it's looking great. One thing I did notice though (and this is probably my fault for not being 100% clear in the brief), is that the word 'travel' or similar isn't carrying over in the output (from the site range to the staff range), there are a couple of examples in the sample data I think. If there's a quick-ish way of fixing this I'd be really grateful, as I don't want to mess up your fabulous code by trying to change things myself.

                  I'm going to work my way through the code and try to understand it, but with my basic knowledge that may take a while. Is it okay if I come back with a one or two questions re. how it works?

                  Thanks once again.

                  Isame

                  Comment


                  • #10
                    Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

                    Hi

                    Thanks for the feedback and yes, happy to answer your question.

                    To fix that issue, replace these section

                    [vb] '//else put the site name
                    Application.EnableEvents = False
                    Cells(Target.Row, Found.Column).Value = Trim(Cells(SiteNames.Row, Target.Column).Value)
                    Application.EnableEvents = True[/vb]

                    with

                    [vb] '//else put the site name
                    Dim SiteDescription As String

                    SiteDescription = Trim(Replace(Target.Value, Found.Value, vbNullString, , , 1))
                    SiteDescription = Trim(Cells(SiteNames.Row, Target.Column).Value & " " & SiteDescription)
                    Application.EnableEvents = False
                    Cells(Target.Row, Found.Column).Value = SiteDescription ' Trim(Cells(SiteNames.Row, Target.Column).Value)
                    Application.EnableEvents = True[/vb]
                    Kris

                    ExcelFox

                    Comment


                    • #11
                      Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

                      Wait..

                      There are couple of changes require. PFA this file.
                      Attached Files
                      Kris

                      ExcelFox

                      Comment


                      • #12
                        Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

                        Thanks, that's picking up and deleting 'travel' entries now. Fantastic

                        I'll be away from my computer for a while - but before I run, two quick questions/queries:

                        i) When I enter a name in the site range, and that person isn't available (i.e. already has an entry for that date in the staff range), it's telling me that the site isn't available (e.g. 'Site 4 isn't available' - rather than the person not being available - nothing major but it would be great if it could be fixed.

                        ii) Could you explain to to me (in really simple terms if possible), how the code is locating and picking up the headers (particularly the "SiteSCell" bit), as when I transfer the code to my 'real data' it's not recognising up the header line and I'll need to modify accordingly.

                        Code:
                        Set Headers = Me.Range("SiteSCell").Resize(, 1000).SpecialCells(xlCellTypeConstants)
                        Sorry about all the questions. Nearly sorted now, hopefully.

                        Many thanks.

                        Isame

                        Comment


                        • #13
                          Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

                          Originally posted by isame View Post
                          i) When I enter a name in the site range, and that person isn't available (i.e. already has an entry for that date in the staff range), it's telling me that the site isn't available (e.g. 'Site 4 isn't available' - rather than the person not being available - nothing major but it would be great if it could be fixed.
                          replace

                          [vb]MsgBox "'" & Cells(Site.Row, Target.Column).Value & "'" & MsgSiteNotAvailable, vbExclamation, MsgBoxTitle[/vb]

                          with

                          [vb]MsgBox "'" & Cells(Site.Row, Found.Column).Value & "'" & MsgSiteNotAvailable, vbExclamation, MsgBoxTitle[/vb]

                          Originally posted by isame View Post
                          ii) Could you explain to to me (in really simple terms if possible), how the code is locating and picking up the headers (particularly the "SiteSCell" bit), as when I transfer the code to my 'real data' it's not recognising up the header line and I'll need to modify accordingly.

                          Code:
                          Set Headers = Me.Range("SiteSCell").Resize(, 1000).SpecialCells(xlCellTypeConstants)
                          I should have told you earlier. SiteSCell is a named range (N5) - starting cell. You need to define this cell(starting header cell) in your original workbook. Also, as you said a blank column will be there to differentiate between staff and site range. I hope this answer your question.
                          Kris

                          ExcelFox

                          Comment


                          • #14
                            Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

                            Hi,

                            Thanks, that's picking up the headers now, and I really like the flexibility of using a named cell as the 'starting point'.

                            I have noticed a problem/unexpected behavior with the rota though, and I'm not quite sure why it's happening or how it might be fixed.

                            The problem occurs with both the test data (with typed data input) and on my 'real data' (which uses dropdown lists), so I don't think it's the data input method per se, but:

                            • if I allocate a person to a site and then change my mind and enter a different name (in the same cell), then (even if I've clicked away from the the active cell for a moment) unless I press the 'delete' key in between, it ends up with 2 (or potentially more) people allocated to the same site.



                            So, I was wondering:
                            • is there a way to stop a user from accidentally doing this, or notify them if they try to?
                            • if not, is there a way to look at that row within the staff range, and flag that (for example) Site 2 occurs twice within that row?


                            I did try a web search to see if anyone had a solution to this issue, but the closest I could find was:
                            http://stackoverflow.com/questions/40538259/excel-vba-delete-data-from-a-worksheet-if-selection-from-dropdown-list-is-chan

                            Any thoughts or help would really be appreciated.

                            Best wishes,

                            Isame

                            Comment


                            • #15
                              Re: On cell value change, lookup header & copy to 2nd range (GBP £30 for commented co

                              I don't think the first option would work. I can restrict the user to assign the same site twice.

                              Replace these section

                              [vb]'//else put the site name
                              Dim SiteDescription As String

                              SiteDescription = Trim(Replace(Target.Value, Found.Value, vbNullString, , , 1))
                              SiteDescription = Trim(Cells(SiteNames.Row, Target.Column).Value & " " & SiteDescription)

                              Application.EnableEvents = False
                              Cells(Target.Row, Found.Column).Value = SiteDescription ' Trim(Cells(SiteNames.Row, Target.Column).Value)
                              Application.EnableEvents = True[/vb]

                              with


                              [vb]'//else put the site name
                              Dim SiteDescription As String
                              Dim StaffRow As Range

                              Set StaffRow = Intersect(Staff, Target.EntireRow)

                              SiteDescription = Trim(Replace(Target.Value, Found.Value, vbNullString, , , 1))
                              SiteDescription = Trim(Cells(SiteNames.Row, Target.Column).Value & " " & SiteDescription)

                              Application.EnableEvents = False
                              If Application.WorksheetFunction.CountIf(StaffRow, SiteDescription) Then
                              MsgBox "'" & Cells(Site.Row, Target.Column).Value & "' is already assigned.", vbExclamation, MsgBoxTitle
                              GoTo UndoAndExit
                              End If
                              Cells(Target.Row, Found.Column).Value = SiteDescription ' Trim(Cells(SiteNames.Row, Target.Column).Value)
                              Application.EnableEvents = True[/vb]
                              Kris

                              ExcelFox

                              Comment

                              Trending

                              Collapse

                              • maichal
                                auto generate id in user form save sale purchase data on sale & purchase sheet
                                maichal
                                i am add add sale transaction & add purchase transaction command button & the id was Auto generate in text box 1 if i am choose sale in combo box 1 than sale-001 id was Auto generate in text box 1 add the name in combo box 2 & click on add sale transaction command button the data was add on the sale sheet,if i am choose Purchase in combo box 1 than Purc-001 id was Auto generate in text box 1 add the name in combo box 2 & click on add Purchase transaction command button the data was add on the Purchase sheet, please solve this sir, after add the information in sale & purchase excel sheet, i am choose sale in combo box 1 the id was auto generate in id text box 1, than i am choose the name in combo box 2, if i am choose sale in combo box 1 than combo box 2 show only sale Customer...
                                3 days ago
                              • DiogoCuba
                                US$20 to Automate Crew Roster
                                DiogoCuba
                                Hi, I have a Crew Roster that controls the crew changes of a certain vessel and I would like to automate it to avoid wasting time with manual copy and paste.

                                Anybody willing to help me?

                                Some of the actions I need:
                                • Create a Pax list and a Flight Manifest based on the personnel assigned to embark and disembark;
                                • Generate a Daily POB based on the names that are onboard (Including the dates of embark and a counter to count how many days the person is onboard);
                                • Compare the Daily POB to a criteria to see if the safe manning is compliant or not;
                                • Create a PAX LIST for the next 7 days automatically so that I can see who is crew change and who is not - this should be triggered based on the dates;
                                • Auto generate Flight Manifest when I click a button and input the
                                ...
                                June 27th, 2017, 09:47
                              Working...
                              X