Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Formula to look up and copy

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Hiya,

    I need a formula that will sit in a workbook called "John Smith" and look into another workbook called "Data". What it needs to do is look down column A in a sheet called "Raw" and when it finds the name "Abi Peshwar" it needs to show all the data that is next to her name, about 8 cells worth.

    So basically in the workbook "Data", we have the data relating to "Abi Peshwar". It takes up a whole row. The thing is, the position of her details may change every day so it could be in row 12 one day and 70 the next day. What I need the "John Smith" workbook to do is look down Column A in the "Raw" sheet in the "Data" workbook, and when it finds "Abi Peshwar", to reproduce the entire row of data back in the "John Smith" workbook

    Hope this is clear, if not tell me

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Audiojoe

    Have you tried VLOOKUP?





  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yeah, it's what we're using at the moment, but it's just so long winded

    Never mind, thanks anyhow

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Audiojoe

    Just an idea,
    say Abi Peshwar is currently in A14 of
    your workbook Data.
    In workbook John Smith (in A1 or wherever) put the formula =[DATA.xls]Raw!A14 and scroll to the right. (basically just linking - this should work if Abi Peshwar is only moving up a down because of row inserts or deletes)

    All the best
    Derek

    [ This Message was edited by: Derek on 2002-03-19 04:07 ]

    [ This Message was edited by: Derek on 2002-03-19 04:07 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cheers Derek,

    I now have two methods and they both work great. Thanks for your input


  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Audiojoe,

    if those 8 cells (say by 100 rows or whatever) are a named range (ie "nufc", the VLOOKUP formula is thusly :

    =VLOOKUP(A1,DATA.xls!nufc,1,false) in your first column

    and

    =VLOOKUP(A1,DATA.xls!nufc,2,false) in your second column

    and

    =VLOOKUP(A1,DATA.xls!nufc,3,false) in your third column

    etc etc

    if this is still too longwinded, anchor the A1 as $A1 and rather than copying across 8 times, substitue the 1,2,3,4 etc as "column()"

    so if your first vlookup occurs in column A :

    =VLOOKUP($A1,DATA.xls!nufc,column(),false)

    and copy across

    if your first vlookup occurs in column B :

    =VLOOKUP($A1,DATA.xls!nufc,column()-1,false)

    and copy across

    or if your first vlookup occurs in column c :

    =VLOOKUP($A1,DATA.xls!nufc,column()-2,false)

    etc etc

    in fact, you can copy the above formula and paste special unicode text into the cell in question then drag it across, changing the named range appropriately, if that's not too long-winded



    Chris









Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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