Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all,

    Can someone let me know how can I copy vales from another workbook i.e. Book 1 Has Values on Cell B1, B2, B3 etc how can I dispaly those values on Cells C1, C2 Etc on book 2 but the values to be sorted from smallest to the biggest number.?

    Kind Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Open the source workbook and the target workbook.

    Start entering the following formula in C1 in the target sheet:

    =SMALL(

    Switch to the source sheet and point to the source data, let's say B1:B50.

    Finish the formula by typing

    ,ROW())

    You should see something like

    =SMALL([Book1.xls]Sheet1!$B$1:$B$50,ROW())

    Fill down as far as needed.

    Now close the source workbook. Excel should automatically add the path of the source workbook to the formulas.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795276' date='28-Sep-2009 14:48']Open the source workbook and the target workbook.

    Start entering the following formula in C1 in the target sheet:

    =SMALL(

    Switch to the source sheet and point to the source data, let's say B1:B50.

    Finish the formula by typing

    ,ROW())

    You should see something like

    =SMALL([Book1.xls]Sheet1!$B$1:$B$50,ROW())

    Fill down as far as needed.

    Now close the source workbook. Excel should automatically add the path of the source workbook to the formulas.[/quote]

    Hi Hans

    I Have tried your formula but I get an error in the end of the last cell, the attached are the two files that i am working on. And also the values are sorted from the smallest number but the names associated with those values stay on the same place/ do not move or change.

    Thanks again
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That's what you get if you provide incomplete and incorrect information. You didn't mention that you wanted to sort more than column B, and your data start in row 2, not in row 1 as you stated.

    See Help to fully understand this formula - MrExcel Message Board for the complicated formulas needed to do what you want.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795285' date='28-Sep-2009 15:30']That's what you get if you provide incomplete and incorrect information. You didn't mention that you wanted to sort more than column B, and your data start in row 2, not in row 1 as you stated.

    See Help to fully understand this formula - MrExcel Message Board for the complicated formulas needed to do what you want.[/quote]

    T Hans

  6. #6
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello again,

    I have been trying to sort out this problem and still did not manage to sort it out, I had a look at MrExcel Message Board as advised by Hans but did not manage to achieve the results that I need. I fsomeone could help I would really appreciate it.

    Kind Regards

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See the workbooks in the attached zip file. I added a column with auxiliary formulas to each of the sheets to prevent the formulas from becoming too complicated; you can hide these columns if you like.

    [attachment=87327:Book1and2.zip]
    Attached Files Attached Files

Posting Permissions

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