+ Reply to Thread
Results 1 to 5 of 5

Help needed with formula linking two workbooks

  1. #1
    Registered User
    Join Date
    03-01-2005
    Posts
    11

    Help needed with formula linking two workbooks

    Hi

    I use Excel to write the rota for my workplace (an intensive care unit), with each row being one persons rota. The data is either a "D" (day shift) a "N" (night shift) or an empty cell (day off). This all works very nicely, but it would be really useful if I were able to export that data to a series of other documents/sheets to form lists of who is working on a particular day. For example:

    May
    -------- 1 ---- 2 ---- 3 ---- 4 ---- 5
    Dave --N------N--- -N-----------
    Mike --D------D--- ---------D------D
    Phil ---D------D---- --------N------N
    Cath ---------D---- -D------D-------
    Carl ---N--- -N---- -N--------------


    This data would then be used to form 5 printed lists (one for each day on the rota) with the 1st one reading:

    1st May
    Day Shift
    :
    Mike
    Phil

    Night Shift:
    Dave
    Carl

    Basically the crux of what I need to do is create a list in one workbook "Staff" that draws data from another workbook "Rota". Each name in the list is entered in a new cell.
    Basically everyone with a "D" in a cell on a particular day in "rota" will get entered on that days "staff" list. Filling in the 1st cell in the list in "Staff" is straightforward, but I am just using formulas, and am not sure how to make the cell below know that the 1st name in the list has already been entered, and to move on to the next name.
    I cannot just have a list formatted the same (ie same number of cells) as the original workbook as with 70 staff this would have too many gaps and not look neat printed.
    I suspect a macro may be required which is beyond me. Thanks.
    David

  2. #2
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    Sub myMacro()

    Set mySht = Worksheets("Sheet3")
    Set myInputRng = mySht.Range("B2:B6") ' the column for a particular day for all employees
    Set myNameRng = mySht.Range("A2:A6") ' range of names of the employees

    Set myOutputDayRange = mySht.Range("B10") ' output of day shift starts in this cell
    Set myOutputNightRange = mySht.Range("B25") ' output of night shift starts in this cell

    j = 0
    For i = 1 To myNameRng.Count
    If myInputRng(i, 1) = "d" Then
    myOutputDayRange.Offset(j, 1) = myNameRng(i, 1)
    j = j + 1
    End If
    Next i

    j = 0
    For i = 1 To myNameRng.Count
    If myInputRng(i, 1) = "n" Then
    myOutputNightRange.Offset(j, 1) = myNameRng(i, 1)
    j = j + 1
    End If
    Next i

    End Sub


    - Mangesh

  3. #3
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    Here's an option (see enclosed file):
    =INDEX($A$4:$A$8;SMALL(IF((LEFT(H$4,1)=B$4:B$8),ROW(INDIRECT("1:"&ROWS(B$4:B$8)))),ROWS($G$5:$G5)))

    Hope it can be of use
    Ola Sandström


    Note:
    This is an Array formula so the formula must be Confirmed by holding down Ctrl+Shift then hit Enter. Otherwise Dave will only come up.

    It's a bit complex but basically this it how it works
    1. it looks for D och N
    a) If it finds D then find the row number of [Row(Indirect...) is just a way to create an Index 1,2,...,5]
    b) Otherwise False
    So Now I have an Array (False,2,3,False,False)
    2. Next step is to take the First occurance (index 2) and put it in row 1 then take the next occurance (index 3) and put that in row 2. Rows(...) will provide the number 1 and the number 2.
    Finally Index(..., picks out Mike to row 1 and Phil to row 2

    Simple :-)?
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-01-2005
    Posts
    11
    Thanks mangesh_yadav:
    This looks like exactly what I am after. However, I am a bit rubbish at VBA and macros in general.

    With the first bit of code:
    How do I set it so that mysht will point to a specific path (eg G:/rota/source.xls)
    and also so that myoutputDayRange points to a different workbook. Basically I want to macro to draw date from one workbook and output to another.

    Your code:

    Sub myMacro()

    Set mySht = Worksheets("Sheet3")
    Set myInputRng = mySht.Range("B2:B6") ' the column for a particular day for all employees
    Set myNameRng = mySht.Range("A2:A6") ' range of names of the employees

    Set myOutputDayRange = mySht.Range("B10") ' output of day shift starts in this cell
    Set myOutputNightRange = mySht.Range("B25") ' output of night shift starts in this cell

    ...

  5. #5
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    The following macro needs you to keep all the files in question open. In which case you don't need their paths:

    Sub myMacro()


    Set mySource = Workbooks("Source.xls").Worksheets("Sheet1")
    Set myDest = Workbooks("Destination.xls").Worksheets("Sheet1")

    Set myInputRng = mySource.Range("B2:B6") ' the column for a particular day for all employees
    Set myNameRng = mySource.Range("A2:A6") ' range of names of the employees

    Set myOutputDayRange = myDest.Range("B10") ' output of day shift starts in this cell
    Set myOutputNightRange = myDest.Range("B25") ' output of night shift starts in this cell

    j = 0
    For i = 1 To myNameRng.Count
    If myInputRng(i, 1) = "d" Then
    myOutputDayRange.Offset(j, 1) = myNameRng(i, 1)
    j = j + 1
    End If
    Next i

    j = 0
    For i = 1 To myNameRng.Count
    If myInputRng(i, 1) = "n" Then
    myOutputNightRange.Offset(j, 1) = myNameRng(i, 1)
    j = j + 1
    End If
    Next i


    End Sub


    - Mangesh

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1