Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

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

  1. #1
    Join Date
    12th August 2014
    Posts
    18

    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)
    • 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
    • If they aren’t available the user should be notified of this via a message box, and the entry should not be allowed.
    • 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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by isame; 3 Weeks Ago at 10:01. Reason: old code removed

  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,521

    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.

  3. #3
    Join Date
    12th August 2014
    Posts
    18

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

    Thanks, that would be great.

  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,521

    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 ?

  5. #5
    Join Date
    12th August 2014
    Posts
    18

    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

  6. #6
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,521

    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.

  7. #7
    Join Date
    12th August 2014
    Posts
    18

    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

  8. #8
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,521

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  9. #9
    Join Date
    12th August 2014
    Posts
    18

    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

  10. #10
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,521

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: December 20th, 2015, 23:04
  2. Replies: 1
    Last Post: July 9th, 2015, 02:11
  3. Sort columns then insert row and copy header at change in name
    By babulikrishna in forum Excel General
    Replies: 4
    Last Post: August 24th, 2011, 00:08
  4. Change Cells Comments Commented By Text
    By Madhart in forum Excel General
    Replies: 8
    Last Post: December 29th, 2008, 14:29
  5. Lookup Intersecting Cell Of Row and Column Header
    By Tachybana in forum Excel General
    Replies: 3
    Last Post: October 17th, 2007, 12:53

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno