Sort Data only/auto add row

Hello all,
Kind new at this execl stuff and help would be appricated.
Thanks in advance.

Need a couple of things here...mybe I'm reaching for the stars?

In the execl zip attached I've got a column and a couple of rows locked to prevent users from changing them. User do have permission to change the data in the cells which is how I needed to be. Question: is their a way to auto sort the data only with out altering the sheet structure and color? What I'm looking for is when a user inputs a date on the left column (including the data in that row), all the data in that row will sort by date (on the left column) automaticlly.

My other help request would be...if I shorten the sheet to about 10 rows and data is entered in the 9th row can a row (folowing the color scheme) be added automaticlly so the sheet never has one empty row at the bottom?

By the way if you need to unprotect the sheet the password is "test".

Originally Posted by su_jumptd

Hello all,
Kind new at this execl stuff and help would be appricated.
Thanks in advance.

Need a couple of things here...mybe I'm reaching for the stars?

In the execl zip attached I've got a column and a couple of rows locked to prevent users from changing them. User do have permission to change the data in the cells which is how I needed to be. Question: is their a way to auto sort the data only with out altering the sheet structure and color? What I'm looking for is when a user inputs a date on the left column (including the data in that row), all the data in that row will sort by date (on the left column) automaticlly.

My other help request would be...if I shorten the sheet to about 10 rows and data is entered in the 9th row can a row (folowing the color scheme) be added automaticlly so the sheet never has one empty row at the bottom?

By the way if you need to unprotect the sheet the password is "test".

Hi,

First create a dynamic range name called 'Shift' as follows. This will automatically extend as new rows are added.
Use the Insert Name Define option, put the word 'Shift' in the Name Box, and the following formula in the refers to box:

Please Login or Register  to view this content.

Then Add and close the Name box.

Now Click the whole column D and name it in the name box 'ShiftDate'

Now Add the following code to the Worksheet_Change event of sheet1 in the VBE (Visual Basic Environment). i.e. Alt-F11 to open the VBE, then find the sheet1 in the VBA Project Window on the left, double click on the Sheet1 name and in the window that opens on the right, pick the left hand drop down box, pick the Worksheet option, and then in the right hand drop down, pick the Worksheet_Change event (not the Worksheet_SelectionChange event), and add the code below:

Please Login or Register  to view this content.

When you enter a date in column D the range called 'Shift', the whole table will sort.

You've probably noticed that this causes the colours of the alternative cells to get out of line if the alternate sort order has changed. You need to set the conditional formats so that the pyjama stripe colours will always alternate whatever the sort. To do this format ALL the cells with one or other of teh colours.

Now go to conditional formatting for D7 and enter the following in the Formula is option.

Please Login or Register  to view this content.

and set the format colour of this cell to the other colour. What this does is use the conditional format when the row is an odd number. Now copy the formats of D7 to all the other cells.

I'm not sure what you mean when you say you want to avoid an empty row at the bottom. Can you explain.

Rgds

Thanks for the help.
I think we're on the right track. Let me see if I can explain myself a little better.

Maybe starting with baby steps.
I'd like to add to what I already have, lets work on one thing at a time maybe that will work better.
K, lets try this 1st. I'd like to have a table (a little shorter) from row 7-17 that always shows those rows by default, even with no data. When user start to enter data and reach the 15th row, two more rows are added automaticly, so their can always be two empty rows no matter how long the table gets.

New shorter table attached.

Originally Posted by su_jumptd

Thanks for the help.
I think we're on the right track. Let me see if I can explain myself a little better.

Maybe starting with baby steps.
I'd like to add to what I already have, lets work on one thing at a time maybe that will work better.
K, lets try this 1st. I'd like to have a table (a little shorter) from row 7-17 that always shows those rows by default, even with no data. When user start to enter data and reach the 15th row, two more rows are added automaticly, so their can always be two empty rows no matter how long the table gets.

New shorter table attached.

Hi,

Here's the first step solution. The Worksheet Change macro reacts to the count of the number of cells containing values in column D. If this is >=12 (which included the header rows) AND a new entry is made in column D, then a new row is added.

Regards