+ Reply to Thread
Results 1 to 7 of 7

Returning Correct Cell with Variable Information

Hybrid View

  1. #1
    Registered User
    Join Date
    09-24-2009
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    Excel 2003
    Posts
    10

    Returning Correct Cell with Variable Information

    I need some help here. I currently have a table with 5 products ("Inserts" 1 through 5) in the columns, and the corresponding volume and weight of each of the different 5 inserts in the rows. The volume and weight of each insert is completely variable based on inputs put in by the user on another worksheet, and there is no relationship between the weight and volume (hopefully the table will post correctly).
    1 Insert 2 Inserts 3 Inserts 4 Inserts 5 Inserts
    Weight 0.081926457 0.109235277 0.250899776 0.051204036 0.150198505
    Volume 75000 765445 1121151.667 1121151.667 1121151.667
    Total 6144.484312 83613.59634 281296.7021 57407.49023 168395.3047
    % of Total 0.010294724 0.140090 0.47129619 0.096182896 0.282136494

    I need a way to sort this data in another table by volume, and have the corresponding weight be sorted with the volume. This must be done automatically without the user needing to go in and sort by hand. Currently I sort the volume by using the LARGE function and then pull the correct weight by using a long IF statement (IF(the sorted cell)=(1st cell from original table),(corresponding weight), IF(the sorted cell)=(2nd cell from original table),(corresponding weight), etc.
    The problem is if the volumes for 2+ of the inserts are the same, it will return the weight from the first insert at that volume, for each of the inserts with that volume (if 3 inserts have the same volume, it will return the weight of one insert for all 3 inserts at that volume) instead of giving me the weight that corresponds to each particular insert.
    Any ideas? Since the weight and volume can change I can't reference a particular cell. I'm in a huge jam here so any help would be GREAT! Thanks in advance!

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Returning Correct Cell with Variable Information

    Welcome to the forum.

    Post a workbook and explain in context.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    09-24-2009
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Returning Correct Cell with Variable Information

    Here's the table from my problem. Unfortunately I cannot upload the full working spreadsheet due to privacy issues, however please keep in mind that the weights and volumes do not stay constant and are continually changed in another worksheet. You can see that there are 3 different inserts with the same volume but different weights (in table at left). However the table at right which should be displaying the weights for each insert, displays the weight of the first insert at that volume for each of the three inserts.
    Attached Files Attached Files

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Returning Correct Cell with Variable Information

    Among equal volumes, in what order should weights appear?

    To put another way, what would be the secondary sort criteria?

  5. #5
    Registered User
    Join Date
    09-24-2009
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Returning Correct Cell with Variable Information

    Among inserts with the same volume the order the weights appear does not matter. They just all need to be there.

  6. #6
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Returning Correct Cell with Variable Information

    Something like this?
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-24-2009
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Returning Correct Cell with Variable Information

    IT LOOKS LIKE IT WORKS!!! Thank you for the help. I've been pulling my hair out for the past 3 days, and was about to give up on a project I've dropped about 80 hours into, because I could not find a work around. You saved my butt!!! Thanks again!!!

+ 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