Excel Question: Convert formula result to cell value

[gstelmack]

Leadup story: My wife and I bought a 400-disc DVD changer. We now have an Excel spreadsheet with all our DVDs organized by slot number. What we want is to be able to sort that list alphabetically while retaining the slot numbers.

It's easy to add a column that has all the slot numbers by setting the first entry in a new column to "1", the second entry to "B2+1", then copying and pasting that into the whole column. But if we sort, that column stays the same.

So how do I convert that column from being the formula to being the results of the formula?

My web search on this turned up all kinds of funky pages with all kinds of tips for everything but this. I know it's buried there, but hard to form the question so the search engine will take me right to it.

[KWhit]

Cut it and then "Paste as Value"

[KWhit]

Sorry. Better explanation: Cut the area you want to "convert." Right click on a different cell. Select "Paste Special." Then Select "Paste Value."

[Klinglerware]

Or you can highlight the column, copy it, and "Paste as Value" on the same highlighted area...

[RPI-Fan]

Why don't you just "Fill" the values for slot numbers?

i.e.:

1. Add your column for slot #'s
2. Type 1 for the first slot
3. Assuming you have the slots correctly, ordered click and drag the bottom-right corner of the "1" cell (there'll be a small dot there where you need to initially cick) all the way down to the 400th slot.

That will give you 1-400 in values and not formulae.


Nevertheless, the "Paste-as-Value" suggestion will work as well.

[dervack]

well, if you have 1 in the first column, and the title in the second, why don't you highlight both columns, then click sort under tools and sort by column b.

[RPI-Fan]

Originally Posted by dervack

well, if you have 1 in the first column, and the title in the second, why don't you highlight both columns, then click sort under tools and sort by column b.

The impression I get is that the formulae are maintained when this is done and therefore his numbers essentially don't sort.

[dervack]

Originally Posted by RPI-Fan

The impression I get is that the formulae are maintained when this is done and therefore his numbers essentially don't sort.

gotcha. I guess I was confused on the need for a formula.

[gstelmack]

Thanks all for the help!