Jump to content

A simple numbered list in Excel 2003


bizzybody

Recommended Posts

I want to make a worksheet in Excel 2003 that's 12 columns by 35 rows. Six columns of numbers separated by six columns of text.

What I have right now is the above laid out on sheet one and on sheet two I have all the text entries in column A. I sort that column then copy and paste 1-35, 36-70 etc. work and tedium. Bleah. The last version of Excel I used much was 97 (I even took a course on it, 13 years ago). I figured by 2003 Excel ought to be able to alphabetize a discontinuous selection, but it still can't. If I select the whole list and have it sort starting with column A it scrambles the numbers (no leading zeros, and I don't want those). If I have it start with column B it won't do anything.

What I want to be able to do is enter text in the text cells and be able to alphabetize it without doing anything to the number columns. Shuffling through 13 year old memories of Excel 97, I thought about linking cells between sheets. Should be able to link 1-35 of column A on Sheet 2 to 1-35 of column B on Sheet 1, 36-70 of A on 2 to 1-35 of B on 1 - and so on. With that I should be able to input text in A on 2 and have it automatically change the text on Sheet 1. No math formulas required, just make what text is entered in a cell on Sheet 2 pop into its linked cell on Sheet 1.

Is there a quick way to do that without having to do 210 individual cell links?

'Course it would be simplest if I could just control+click the six text columns and bang the A>Z button and have it sort across the gaps, but Excel 2003 isn't that fancy. Seems to be such an obvious and useful feature, but apparently not to Microsoft.

The most elegant solution would be a one sheet list with self contained "magic" so that text could be entered into the highest numbered empty cell and all the columns auto-alphabetize when Enter is hit.

Link to comment
Share on other sites


I poked around some more and found this works. Select the range on Sheet 2 to be linked. Select the range on Sheet 1 to link to. Click Paste Special on the Edit menu then click the Paste Link button. Repeat for the other five columns.

Now any changes made in column A on Sheet 2 get automatically transferred to Sheet 1. Still not as simple for the user as a contained on one sheet list that can be edited in-place with alphabetizing happening right there. That I'm still interested in doing if possible.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...