Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

lee_1133's Avatar
lee_1133 lee_1133 is offline
Member with 274 posts.
THREAD STARTER
 
Join Date: Aug 2002
Location: wales, UK
10-Jul-2006, 03:52 PM #1
How do you Auto arrange cells in excel based on their values?
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
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,565 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
10-Jul-2006, 03:57 PM #2
can you post an example
i would thought all you need to use is a sort - but it may not be that simple
lee_1133's Avatar
lee_1133 lee_1133 is offline
Member with 274 posts.
THREAD STARTER
 
Join Date: Aug 2002
Location: wales, UK
10-Jul-2006, 04:14 PM #3
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?
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
10-Jul-2006, 05:03 PM #4
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.
lee_1133's Avatar
lee_1133 lee_1133 is offline
Member with 274 posts.
THREAD STARTER
 
Join Date: Aug 2002
Location: wales, UK
10-Jul-2006, 05:14 PM #5
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
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
10-Jul-2006, 05:28 PM #6
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.
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
10-Jul-2006, 05:36 PM #7
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
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
10-Jul-2006, 05:37 PM #8
Sorry Bomb - I was typing at the same time as you.
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
10-Jul-2006, 05:44 PM #9
Or so I thought :-)
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
10-Jul-2006, 05:46 PM #10
Quote:
Originally Posted by The Villan
Or so I thought :-)
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.
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
10-Jul-2006, 05:55 PM #11
Did I? LOL When was that?
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
10-Jul-2006, 05:58 PM #12
IIRC you did a macro to create a basic p/t on the fly from a large dataset, complete with instructions/explanation in Word.
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
10-Jul-2006, 06:02 PM #13
are you referring to this one? See upload
Attached Files
File Type: xls Pivot table1.xls (42.5 KB, 1411 views)
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
10-Jul-2006, 06:06 PM #14
Yup, 'cept there's no macro(s). /
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
10-Jul-2006, 06:10 PM #15
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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑