1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

How do you Auto arrange cells in excel based on their values?

Discussion in 'Business Applications' started by lee_1133, Jul 10, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. lee_1133

    lee_1133 Thread Starter

    Joined:
    Aug 24, 2002
    Messages:
    274
    Hi,

    Got a few questions which tie in to what i am doing so i'll try to explain as best i can.

    I want to be able to have excel automatically arrange the order in which it displays the information based on the value or information in a cell. Eg i have a list of things i sell online and I track their performance using the spreadsheet were i have a sheet for every product type which are linked to a master snapshop sheet which has the prices, weight and other info about the items. The product name on this main sheet is hyperlinked to the sheet containing the product and in turn the data in that sheet is formulated back to the main snapshot the aim being as little data input as possible to calculate everything.
    I would like to be able to have the snapshop mainsheet automatically put for example the best performing product at the top of the list. At the moment a have to select the cell and expand the data range but this is erratic as i don't think i do it properly. Sometimes it messes all the data up. I am not that good with excel so i am probably going to be told why don't you do this or that etc but that is ok if anyone has an idea.
    Anyone able to help?
    Apologies if its difficult to follow but i will give more info if requested.
    If someone has a link to a webpage that can help would be great also.

    Regards,
    lee_1133
     
  2. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,958
    can you post an example
    i would thought all you need to use is a sort - but it may not be that simple
     
  3. lee_1133

    lee_1133 Thread Starter

    Joined:
    Aug 24, 2002
    Messages:
    274
    Hi,

    What i want it to do is automatically ammend the order of the data to show the most profitable item at the top (without me needing to manually highlight the cells and sort A-Z or Z-A. So that as the sales change so do the order in which they appear in the list. Is it possible?
     
  4. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    Click on a cell in the column that you want to be your sort column.
    On your toolbar there are two sort buttons Ascending and descending. Click on the button that you require. Hey presto the data should be sorted.

    This asumes that you do not have blank columns or rows amongst the data that you want sorted. Excel assumes that the first row of data is field headings and leaves that row at the top, and sorts all the other rows below.

    As mentioned before, you should really upload an example so that we can see what the real problem is.
     
  5. lee_1133

    lee_1133 Thread Starter

    Joined:
    Aug 24, 2002
    Messages:
    274
    Ok I think you are getting confused. I know how to use the Acend / Decend tab what i want to know is can the data be set to automatically change position based on the data in the cells.
    Take this example i am selling 10 items.
    item number 5 is the best seller making the most money so i want it to automatically move to the top cell in the list. (No clicking acend / decend) I want it to happen Automatically then if item number 8 become the best seller making the most money when the data chages to reflect this I want item 8 to automatically go to the top of the list.
    Hope that helps
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    If you want it to happen truly automatically you'll need to use code (AKA macros). You could get close to it with formulas, however that wouldn't be 100% safe unless you could guarantee that no 2 products would ever have the same amount.
     
  7. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    You are asking Excel to read your mind. To my knowledge it isn't designed to do that.

    You can write Auto macro's for when you open the spreadsheet. However I really can't see the point when with 2 clicks of the mouse, you have the result you desire.

    I have always said that you can do 90% of your work for you with little effort whilst the remaing 10% requires a lot of effort. Where do you draw the line?

    Maybe somebody has a simple solution that doesn't require a lot of hard work just to geta simple result
     
  8. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    Sorry Bomb - I was typing at the same time as you.
     
  9. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    Or so I thought :)
     
  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    I'm waiting to see where this goes, whether it gets to a point where it would be appropriate for you to link to the pivot table primer you did a while back. (y)
     
  11. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    Did I? LOL When was that?
     
  12. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    IIRC you did a macro to create a basic p/t on the fly from a large dataset, complete with instructions/explanation in Word.
     
  13. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    are you referring to this one? See upload
     

    Attached Files:

  14. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    Yup, 'cept there's no macro(s). :confused: / :D
     
  15. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    You have a good memory Bomb. You must be considerably younger than me :)

    I wa strying to upload the instructions, but getting an error on upload.


    You may have a point, but we need to see the spreadsheet so that we take the right direction.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/482059