Results 1 to 12 of 12

Thread: Vlookup ?? (XP)

  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Vlookup ?? (XP)

    Hi there
    I'm writing this spreadsheet for a big event.

    On one of the pages I have all the event details. These are populated by somebody else. I have made a list with artist names, so the yellow boxes are dropdowns.

    However, on other pages (at least 75 of them) I need the info as shown as worksheet 2. If I can't have information of the left of a Vlookup, how do I get the info ??

    I select the artist and the other info should populate automatically.

    Is there an easy solution ??

    Thanks
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup ?? (XP)

    Will each artist perform only once, or will some artists perform more than once, for example on different stages?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Vlookup ?? (XP)

    Most of them will perform at least 3 times, usually at the same stage, but not necessarily. There are 10 stages and 5 days

    Sorry about the delay getting back to you.. Time difference and all....

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup ?? (XP)

    Multiple days ... could you post a somewhat more representative workbook (with dummy data)?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Vlookup ?? (XP)

    On the first page I have the data the artist perform
    On the second page how I want it to look like.

    Thanks

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Vlookup ?? (XP)

    On the first page I have the data the artist perform
    On the second page how I want it to look like.

    Thanks

    PS it says there was a problem, so this might be posted twice, excuses for that
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup ?? (XP)

    I think I'm starting to repeat myself. This should have been done in Access, not in Excel.

    But anyway, here is a macro you can use (I don't see an easy way to use formulas here):
    <code>
    Sub ArtistSchedule()
    Dim strArtist As String
    Dim wsh1 As Worksheet
    Dim wsh2 As Worksheet
    Dim rngFind As Range
    Dim strAddress As String
    Dim lngSourceRow As Long
    Dim lngSourceCol As Long
    Dim lngTargetRow As Long
    Dim lngSearchRow As Long

    Set wsh1 = Worksheets("Sheet1")
    Set wsh2 = Worksheets("worksheet 2")
    strArtist = wsh2.Range("B4")
    wsh2.Range("B7:E100").ClearContents
    lngTargetRow = 6
    With wsh1.UsedRange
    Set rngFind = .Find(What:=strArtist, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows)
    If Not rngFind Is Nothing Then
    strAddress = rngFind.Address
    Do
    lngTargetRow = lngTargetRow + 1
    lngSourceRow = rngFind.Row
    lngSourceCol = rngFind.Column
    ' Date
    lngSearchRow = lngSourceRow
    Do
    lngSearchRow = lngSearchRow - 1
    Loop Until IsDate(wsh1.Cells(lngSearchRow, 2))
    wsh2.Cells(lngTargetRow, 2) = wsh1.Cells(lngSearchRow, 2)
    ' Stage
    wsh2.Cells(lngTargetRow, 3) = _
    wsh1.Cells(lngSearchRow + 2, lngSourceCol)
    ' Start
    wsh2.Cells(lngTargetRow, 4) = wsh1.Cells(lngSourceRow, 2)
    Set rngFind = .FindNext(After:=rngFind)
    Loop While Not rngFind Is Nothing And _
    Not rngFind.Address = strAddress
    End If
    End With
    End Sub
    </code>

    See attached version.
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Vlookup ?? (XP)

    I know you said to me before about Access. However, software is rather expensive here.
    When you buy a package that contains word, excel, powerpower and outlook you pay about NZ$240/NZ$300 (depending if it is a "special" or not)
    When you buy the package that includes Access as well you pay at least NZ$1024 or more.

    Because of the work I do, I have to assume people do not have Access on their machines.

    I will check the macro later on, and again many thanks for all your help

    Kind regards

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup ?? (XP)

    I understand about the expense - IT departments here in The Netherlands are often reluctant to install Office Professional for other than a few select users.
    A cheaper alternative is to give end users Access Runtime - you'd need only one license for the developer edition, then Runtime can be distributed freely.
    But even with Office Professional, the high cost can often be justified by the time a well-designed Access database saves employees.

  10. #10
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Vlookup ?? (XP)

    Does OpenOffice.org (the free, open source version of MS "Office") have an "Access"counterpart to it that could be used instead of MSoft's product?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup ?? (XP)

    OpenOffice.org does have a relational database application, named Base, with tables, queries, forms and reports. I don't know how it compares to MS Access.

  12. #12
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Vlookup ?? (XP)

    Hi Hans......thanks for that info....I downloaded OpenOffice.org a while back, started to install and play with it but got distracted and didn't go back to it....but judging by the 'reviews' you see about OO.org, I gather that it's a pretty impressive product....maybe that might work for Karat.....anyway, have a nice weekend.

Posting Permissions

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