1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: Putting data into correct cell?

Discussion in 'Business Applications' started by ajrobson, Oct 10, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    Attached is an example of what I am trying to do.

    I have a sheet called timetable which lists each person and what they should be doing mon-fri (this is an example the proper one goes on for a number of months)

    I have a sheet called holidays which lists whenever a person is on holiday and gives the start and end date of that holiday.

    Could anybody tell me how I could get it so everything from the holidays sheet is mapped to the correct cell on the timetable sheet? Perhaps some sort of Vlookup?

    Thanks in advance.
     

    Attached Files:

  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    save the attachment as a ".xls" (97-2003_ version). not everyone has 2007, I have it on my work LT, but not here at home so I can't look at it right now.
     
  3. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    Sorry, re-attached it has a .xls
     

    Attached Files:

  4. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    I'm not even sure why you would even want such an arrangement? why aren't you just using the time table sheet and entering "Holiday" on the days they are off?

    Why aren't you using a normal calendar application? Like outlook, or google Calendar? I think this is quite complicated to setup, I see a lot of coding ( don't think formulas will do it). Even though code can do just about anything for you, I don't like to spend time on things which don't seem practical... I could be missing the obvious solution, so maybe you can elaborate more, or someone else can jump in and offer opinion.

    I just see a lot of looping required, and to make it "right", you need validation on the dates.. like is the end date greater than the start date, or what if the exceeds a persons allotment of vacations?
     
  5. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    Because this is an example of the real sheet which goes on for a number of months and as lots more people than this example.

    Because not all computers I will want this data from are connected to the internet, however they can access a network share.

    I've already got the dates validated before they are placed into the holidays sheet, once the data is in the spreadsheet it just needs placing into the corrrect cell on the other sheet.

    Anybody got any ideas?
     
  6. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    ok, I just wanted to get a better understanding.

    When I look at it, I can only see VBA handling this. So starting with Person "A" on timetable sheet first day I need to loop through the holiday sheet when Person A is found... then compare the Start date / end date to all the dates on the row A that are >= than start and <= end, once found mark the cells, and continue.

    I think this will work, but I just need some time to tie it together ( later today)
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    What is the "X, Y, Z" data? Is that part of the "what they should be doing"? Where is that data mapped? Are you wanting a function to return if they are on holiday that day? Or do you want to conditionally format that cell? This can be done with formulas as well as VBA if you'd like. We just need more information on how you want to create the data.

    Is this a shared workbook? Or just a workbook stored on a network location which everyone can access?
     
  8. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    Thanks Ziggy1.

    Zack yes XYZ is what they should be doing on that day. That is not mapped I just enter that myself but as a person is doing the same XYZ for long periods of time I rarely have to update that bit.

    I don't need a return value I just want to write Holiday in any cell on the timetable sheet that corresponds to the correct date(s) on the holiday sheet.
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hmm, ok. Sounds a little clunky. So you want to perform this check whenever you want then? To just overwrite the cells data with "Holiday" if found to be within the holiday range? What about if you have values already in those cells, do you want to overwrite it? Do you want to be asked? Do you want to be asked on every single cell, or once for all cells?
     
  10. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    Ideally I would have a button which would be when it would get holidays from one sheet and put them in the correct cell.

    Yes if values are already in those cells I want to override them, I'm not to bothered about asking if its ok to overwrite cells.
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Okay, so this will be run from this workbook? And there is only one sheet which this will run on? And there is always only two columns of date ranges, B and C? And will the data in the TIMETABLE sheet always be in that structure, and data will continue to the right? When does that data change? Do you just keep adding columns? Do you add another sheet? Another file?
     
  12. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    Yes this will be run from the workbook.
    Yes there is only one sheet.
    Yes there is only ever two date ranges in B and C, although it is possible the same name may be shown twice in column A e.g Person X is on holiday from 20/01/11 to 21/01/11 then again on 23/06/11 04/07/11
    Yes the timetable sheet will always be in that structure and continues to the right for a 18 month period at the end of which I will make a just overwrite this one.
    There will never be another sheet or file.
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    This works in testing on your sheet...

    Code:
    Sub OverlayHolidays()
        
        'Declare variables
        Dim wsH As Worksheet, wsT As Worksheet
        Dim iRow As Long, iLast As Long
        Dim iCol As Long, i As Long
        Dim aFind() As Variant, iCnt As Long
        Dim rFind As Range, rLook As Range
        
        'Set variables
        Set wsH = ThisWorkbook.Sheets("HOLIDAYS")
        Set wsT = ThisWorkbook.Sheets("TIMETABLE")
        Set rLook = wsH.Range("A2", wsH.Cells(wsH.Rows.Count, 1).End(xlUp))
        
        'Temporarily shut off application properties in lieu of efficiency
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        'Loop through TIMETABLE rows
        For iRow = 4 To wsT.Cells(wsT.Rows.Count, 1).End(xlUp).Row
            ReDim aFind(1 To 1000, 1 To 3) As Variant
            iCnt = 1
            
            'Check if person found in HOLIDAYS list, if not then skip
            Set rFind = rLook.Find(What:=wsT.Cells(iRow, 1).Value, LookAt:=xlWhole)
            If rFind Is Nothing Then GoTo SkipPerson
            
            'Loop through HOLIDAYS persons, grab rows of data pertinent
            For i = rLook(1, 1).Row To rLook(rLook.Rows.Count, 1).Row
                If wsH.Cells(i, 1).Value = wsT.Cells(iRow, 1).Value Then
                    aFind(iCnt, 1) = wsH.Cells(i, 1).Value
                    aFind(iCnt, 2) = wsH.Cells(i, 2).Value
                    aFind(iCnt, 3) = wsH.Cells(i, 3).Value
                    iCnt = iCnt + 1
                End If
            Next i
            
            'Loop through columns of TIMETABLE
            For iCol = 2 To wsT.Cells(3, wsT.Columns.Count).End(xlToLeft).Column
                
                'Look at array for this rows person only, check dates
                For i = LBound(aFind) To UBound(aFind)
                    If IsEmpty(aFind(i, 1)) = True Then Exit For
                    If aFind(i, 1) = wsT.Cells(iRow, 1).Value Then
                        If aFind(i, 2) <= wsT.Cells(3, iCol).Value Then
                            If aFind(i, 3) >= wsT.Cells(3, iCol).Value Then
                                
                                'Day is a holiday
                                With wsT.Cells(iRow, iCol)
                                    .Value = "Holiday"
                                    .Interior.ColorIndex = 37
                                End With
                                
                            End If
                        End If
                    End If
                Next i
                
            Next iCol
            
    SkipPerson:
        Next iRow
        
        'Turn back on application properties
        Application.DisplayAlerts = True
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
    End Sub
    HTH
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I guess the 'iLast' variable wasn't used and can be discarded. Do you know how to install/run this code?
     
  15. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    nice work as ussual Zack! I think it works great.

    The only thing you should add to reset the cell colors to purple at the begining so that if a date range is reduced it will update. works ok when increasing the date range.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/955322

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice