Locking rows together in excel worksheet

margo300

I'm not at all a technical person, so would appreciate any answers in easy format.

On my excel spreadsheet I have my data spread across five horizontal cells. (A to E). I know how to sort the normal way by highlighting everything then choosing the "no header" box, then the sort option for the appropriate column. However, this morning I inadvertently missed out column E, and sorted a number of times (and saved the spreadsheet), before realising that column E had not been included. Now my spreadsheet is all up the creek and I will have to re-enter the data. It's a two page length spreadsheet and will take ages.

I know I should have made a back up before I embarked on the exercise but it's too late now. A lesson to me.

My questions are:

1 - After saving a document, is it possible to retrieve an earlier version?

2 - Is there a definitive way of locking horizontal cells together so that no matter which column is sorted, the data in the horizontal row always stays together so that the sort of mistake I made does not occur? (I also need to be able to change data in any one cell from time to time).

I've googled question two, and seen the same question, but no satisfactory answer anywhere.

Many thanks in advance.

rmg1

As a regular user of Excel, I can't think of any way of doing what you ask (you can't go back to a previously saved version of the spreadsheet unless you have version numbers in the filename).
Can you re-sort the data including column E to sort out the problem?

::edit::

Just a thought, if the ranges are going to be static, can you use named ranges?

darich

Depends on how techie you are.....you could write a macro.
The macro would always sort the same cells without you having to highlight them. The downside is that it wil always be the same cells ie if the macro sorts cells a1:e1 then if you add rows above and push your five "linked" rows down, the macro will still sort a1:a5. It is easy to adjust the macro though.

debitcardmayhem

Which version of windows? , in Explorer right click on the spreadsheet and see if you can retrieve earlier version? there might one from your last restore point. Failing that a recovery program eg recuva might find a deleted version but I tried that for my OH yesterday with a spreadsheet and after 2 hours of deep scan found nothing.
Also in respect of backups etc it might be worth using microsoft sync toy and schedule a daily copy for the future

rmg1

The macro route could be good. Easy to write (just record one to start with) and then you could hit one button and it'll all be done for you.
I can do those as well, so if you let know if you want to go that route, I'll help all I can.

debitcardmayhem

As far as linking the cells I will think about that but when i sort my data I always select all ie put the mouse over the top left box over row 1 and left of column A

margo300

Thank you all so much for your swift replies. I've printed off everything and am going through your suggestions one by one. I'll get back later on to let you know how things are going.

For the future, it has been a revelation to me that it is not necessary to physically highlight boxes; that it can be done in one stroke by clicking on the cell on the top left box over row 1 and left of Column A - makes me wonder what other long-winded ways I have of doing things!

I also wonder why I have never thought of saving the spreadsheet as a different version each time I make alterations - that at least would ensure that any mistakes could be quickly rectified by reversion to a previous copy!

Not sure what a macro is unfortunately - obviously I'm way down in the technical field - product of my age I fear.

Really glad I came onto this site!!!

Thanks

debitcardmayhem

margo300 wrote: »

Thank you all so much for your swift replies. I've printed off everything and am going through your suggestions one by one. I'll get back later on to let you know how things are going.

For the future, it has been a revelation to me that it is not necessary to physically highlight boxes; that it can be done in one stroke by clicking on the cell on the top left box over row 1 and left of Column A - makes me wonder what other long-winded ways I have of doing things!

I also wonder why I have never thought of saving the spreadsheet as a different version each time I make alterations - that at least would ensure that any mistakes could be quickly rectified by reversion to a previous copy!

Not sure what a macro is unfortunately - obviously I'm way down in the technical field - product of my age I fear.

Really glad I came onto this site!!!

Thanks

:wave: Welcome , we aim to please, come back and see us regularly

rmg1

Glad to help, we all have to start somewhere.
A macro is just a way of recording what you do so you can do it again a bit quicker and automatically. Once you get your head round them, they're quite straightforward. If not, shout up and we'll all chip in.

Fairwinds

If you have a regular data range i.e no blank columns or rows in the middle of your data , you dont need to select the range at all, as excel will do it for you. The best way is just to have a single cell selected within the range - excel will then automatically select your range - which you can adjust if necessary.

I would suggest that highlighting all cells is NOT a good way to go particularly if you are using a spreadsheet designed by someone else as there could be data elsewhere in the spreadsheet that you are screwing up.

chateauneufdupape

A macro repeats a series of actions that you have recorded. Quite easy to do. So in your case, you would start the macro recording, highlight the cells you want to sort, sort the cells and then stop recording the macro. Next time you want to do a sort, all you need to do is run the macro.

You wil find the macro in tools>macro>record new macro. Try using one on a different unimportant spread sheet until you get the hang of it.

Once you are familiar with recording macros, you can then put a button on your spread sheet to make it very easy to run the macro.