Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    entering data from workbook to workbook using ref (2000)

    how do you get excel to enter a set of data from a different workbook on to a current workbook depending on the content of a cell that matches the content of the other cell in the other workbook (I believe I trying to reference one cell to another)? For Example: Suppose I enter a stringed character in a cell of the current workbook. Let say that same stringed character is in another workbook with other data describing it in adjacent cells. How can I command excel to recognize that string, go to that workbook, then give me the adjacent data cells pertaining to that character? I saw this performed using an IF(OFFSET(NAME!....)) command before, but I don't understand how it works. Any suggetions?

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

    Re: entering data from workbook to workbook using ref (2000)

    You can use the VLOOKUP function for this. Example:

    =VLOOKUP("P2",'C:Excel Files[Test.xls]Sheet1'!$B$2:$D$25,3,FALSE)

    This formula searches for the value "P2" in the first column of the range B225 in the worksheet named Sheet1 in the workbook Test.xls in the folder C:Excel Files.
    It returns the corresponding value from the third column in the range. The last argument FALSE specifies that we are looking for an exact match; if an exact match is not found, the formula returns #N/A.

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: entering data from workbook to workbook using ref (2000)

    Almost there! This works find, however, is there a way to get this cell to use or refer to the character of another cell in the current workbook? - I ask this because I need the name to describe the data being returned. And can I get it to return all adjacent data that correspond to the cell being referred to (returning multiple colunms)? Thanks in advance.

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

    Re: entering data from workbook to workbook using ref (2000)

    I'm afraid I don't understand your first question (English is not my mother tongue). Could you try to explain with an example (either in a post or in an attached workbook)?

    About the second question: you can use similar formulas with different column numbers to retrieve multiple columns.

  5. #5
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: entering data from workbook to workbook using ref (2000)

    Heres an attached example of what I'm trying to do. I will be greatful for any suggestion.

    Thanks

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

    Re: entering data from workbook to workbook using ref (2000)

    You can use VLOOKUP exactly the way I mentioned, only you must change the lookup column. Once you have filled one row with the correct formulas, you can fill down.

    See attached workbook. To prevent the lookup range from shifting, I made the reference absolute: Sheet2!$A$7:$M$19.

  7. #7
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: entering data from workbook to workbook using ref (2000)

    Thanks alot - that really helped!!!

Posting Permissions

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