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!
Bookmarks