inserting rows without disrupting data validation cells 2

[CTaylor1968]

Wonder if anyone can help. We have to record information in a spreadsheet and at the end of each month I have to analyse the data and report to management. For this I use the COUNTIF facility. I found in the past that there were often typos and so very often the analysing took a lot longer than it should because I had to go in and correct all the errors first. So I decided to use the "validation data" option in the cells which are used for the count. The problem now is that my colleague who enters all the data keeps inserting rows because she wants it to be in alphabetical order. WHen she inserts rows it affects the data that I have placed in Cell AB2 (the data for the validated cells), and i now have to keep going in and getting rid of the blank cells. I realise I could move the data from AC2 and place it much further down - maybe AC2000 or something, but I just wondered if there was anything else that could be done? I thought originally it would be good to have the data list in a different worksheet but that isn't possible is it? Is there anyway of setting up the spreadsheet so that when it is saved it will "automatically" revert to alphabetical order? any ideas?

Many thanks - as always!

 

[CBasicAsslember]

I take it that the validation data is a short list. You don't have to link to a cell for the validation data, you can enter it directly in the source box.

 

[CTaylor1968]

The list is 194 rows.
Don't know what you mean about entering it directly in the source box?

 

[CBasicAsslember]

When you set up validation for a cell you can put your list in the "source:" box. Say the valid data was 1 to 5. You could just type:

1,2,3,4,5

Since your list is quite long this would not be very practical.

Put your list below where the data will be entered. For argument sake lets say you start your list runs from A2000 to A2200. If you put your list in the "source" box by selecting the cells you will need to edit it because you would get:

=$A$2000:$A$2200

You need the range to change when she adds rows so it will still point to your list. Removing the $ sign will solve that problem and your list reference should look like this:

=$A2000:$A2200

Now if she adds a row the reference to the list will also change to:

=$A2001:$A2201

 

[GlennUK]

Quote snippet:

I thought originally it would be good to have the data list in a different worksheet but that isn't possible is it?

Yes, it is possible. Name the list ( Insert/Name/Define ), and refer to that name in validation.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[CBasicAsslember]

Good idea Glenn. I would also hide the worksheet that the validation data is on.

Is there anyway of setting up the spreadsheet so that when it is saved it will "automatically" revert to alphabetical order?

In VBA you could do a sort triggered by the before save event.