I have a column with copied formular till row # 3,000. I've entered data into
10 rows.
But, when I filter the data the next row for the data entries is 3,001.

How could I make the next empty row (11) available for the data entries
after applying filtering.

Thanks

I usually copy and paste my filtered data over to another worksheet for
further processing.........

Vaya con Dios,
Chuck, CABGx3


"Alex" wrote in message
...
I have a column with copied formular till row # 3,000. I've entered data
into
10 rows.
But, when I filter the data the next row for the data entries is 3,001.

How could I make the next empty row (11) available for the data entries
after applying filtering.

Thanks

Thanks, CLR.

But, my spreadsheet is for multi-user entries and the idea is that some user
filter his/her data to see only his/her data and continue his/her entries.

"CLR" wrote:

I usually copy and paste my filtered data over to another worksheet for
further processing.........

Vaya con Dios,
Chuck, CABGx3


"Alex" wrote in message
...
I have a column with copied formular till row # 3,000. I've entered data
into
10 rows.
But, when I filter the data the next row for the data entries is 3,001.

How could I make the next empty row (11) available for the data entries
after applying filtering.

Thanks

Alex,

You could

1) put your formulas into a column that is not part of your table (separated
by at least one completely blank column), then just filter your table.

2) only enter your formula to match your data, then set Excel to Extend List
formats and formulas (Tools Options Edit tab - but only for Excel XP and
2003)

3) only enter your formula to match your data, and use a worksheet change
event to copy the formula when an entry is made in a new row.

HTH,
Bernie
MS Excel MVP


"Alex" wrote in message
...
I have a column with copied formular till row # 3,000. I've entered data
into
10 rows.
But, when I filter the data the next row for the data entries is 3,001.

How could I make the next empty row (11) available for the data entries
after applying filtering.

Thanks

Thank you very much, Bernie.

The third one would be just exellent for me.

I have a data entry column for dates and that column with the formular that
just converts the date column into to show month. So, it's B column with =A2
in B2 formatted to show "mmm".

How could I do the third suggested item - enter the data into A2 and only
after that get a formula in B2?

Thanks,

Alex

"Bernie Deitrick" wrote:

Alex,

You could

1) put your formulas into a column that is not part of your table (separated
by at least one completely blank column), then just filter your table.

2) only enter your formula to match your data, then set Excel to Extend List
formats and formulas (Tools Options Edit tab - but only for Excel XP and
2003)

3) only enter your formula to match your data, and use a worksheet change
event to copy the formula when an entry is made in a new row.

HTH,
Bernie
MS Excel MVP


"Alex" wrote in message
...
I have a column with copied formular till row # 3,000. I've entered data
into
10 rows.
But, when I filter the data the next row for the data entries is 3,001.

How could I make the next empty row (11) available for the data entries
after applying filtering.

Thanks

Alex,

Copy the code below, then right-click on the worksheet tab and select "View Code". Paste the code
in the window that appears.

This will only work (as written) when you enter values into one cell at a time.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
Application.EnableEvents = False
With Target(1, 2)
.NumberFormat = "mmm"
.FormulaR1C1 = "=RC[-1]"
End With
Application.EnableEvents = True
End Sub


"Alex" wrote in message
...
Thank you very much, Bernie.

The third one would be just exellent for me.

I have a data entry column for dates and that column with the formular that
just converts the date column into to show month. So, it's B column with =A2
in B2 formatted to show "mmm".

How could I do the third suggested item - enter the data into A2 and only
after that get a formula in B2?

Thanks,

Alex

"Bernie Deitrick" wrote:

Alex,

You could

1) put your formulas into a column that is not part of your table (separated
by at least one completely blank column), then just filter your table.

2) only enter your formula to match your data, then set Excel to Extend List
formats and formulas (Tools Options Edit tab - but only for Excel XP and
2003)

3) only enter your formula to match your data, and use a worksheet change
event to copy the formula when an entry is made in a new row.

HTH,
Bernie
MS Excel MVP


"Alex" wrote in message
...
I have a column with copied formular till row # 3,000. I've entered data
into
10 rows.
But, when I filter the data the next row for the data entries is 3,001.

How could I make the next empty row (11) available for the data entries
after applying filtering.

Thanks

Thanks a lot again, Bernie. It's working perfectly.

How about if I want to have it empty if in the major (Date) column the data
has been deleted.
I'm trying this:
..FormulaR1C1 = "=IF(RC[-1]='','',RC[-1])" But, it's not working.

Thanks

"Bernie Deitrick" wrote:

Alex,

Copy the code below, then right-click on the worksheet tab and select "View Code". Paste the code
in the window that appears.

This will only work (as written) when you enter values into one cell at a time.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
Application.EnableEvents = False
With Target(1, 2)
.NumberFormat = "mmm"
.FormulaR1C1 = "=RC[-1]"
End With
Application.EnableEvents = True
End Sub


"Alex" wrote in message
...
Thank you very much, Bernie.

The third one would be just exellent for me.

I have a data entry column for dates and that column with the formular that
just converts the date column into to show month. So, it's B column with =A2
in B2 formatted to show "mmm".

How could I do the third suggested item - enter the data into A2 and only
after that get a formula in B2?

Thanks,

Alex

"Bernie Deitrick" wrote:

Alex,

You could

1) put your formulas into a column that is not part of your table (separated
by at least one completely blank column), then just filter your table.

2) only enter your formula to match your data, then set Excel to Extend List
formats and formulas (Tools Options Edit tab - but only for Excel XP and
2003)

3) only enter your formula to match your data, and use a worksheet change
event to copy the formula when an entry is made in a new row.

HTH,
Bernie
MS Excel MVP


"Alex" wrote in message
...
I have a column with copied formular till row # 3,000. I've entered data
into
10 rows.
But, when I filter the data the next row for the data entries is 3,001.

How could I make the next empty row (11) available for the data entries
after applying filtering.

Thanks

Alex,

Better to clear the formula, for the filtering to work correctly:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
Application.EnableEvents = False
With Target(1, 2)
If Target.Value < "" Then
.NumberFormat = "mmm"
.FormulaR1C1 = "=RC[-1]"
Else
.ClearContents
End If
End With
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP


"Alex" wrote in message
...
Thanks a lot again, Bernie. It's working perfectly.

How about if I want to have it empty if in the major (Date) column the data
has been deleted.
I'm trying this:
.FormulaR1C1 = "=IF(RC[-1]='','',RC[-1])" But, it's not working.

Thanks

"Bernie Deitrick" wrote:

Alex,

Copy the code below, then right-click on the worksheet tab and select "View Code". Paste the
code
in the window that appears.

This will only work (as written) when you enter values into one cell at a time.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
Application.EnableEvents = False
With Target(1, 2)
.NumberFormat = "mmm"
.FormulaR1C1 = "=RC[-1]"
End With
Application.EnableEvents = True
End Sub


"Alex" wrote in message
...
Thank you very much, Bernie.

The third one would be just exellent for me.

I have a data entry column for dates and that column with the formular that
just converts the date column into to show month. So, it's B column with =A2
in B2 formatted to show "mmm".

How could I do the third suggested item - enter the data into A2 and only
after that get a formula in B2?

Thanks,

Alex

"Bernie Deitrick" wrote:

Alex,

You could

1) put your formulas into a column that is not part of your table (separated
by at least one completely blank column), then just filter your table.

2) only enter your formula to match your data, then set Excel to Extend List
formats and formulas (Tools Options Edit tab - but only for Excel XP and
2003)

3) only enter your formula to match your data, and use a worksheet change
event to copy the formula when an entry is made in a new row.

HTH,
Bernie
MS Excel MVP


"Alex" wrote in message
...
I have a column with copied formular till row # 3,000. I've entered data
into
10 rows.
But, when I filter the data the next row for the data entries is 3,001.

How could I make the next empty row (11) available for the data entries
after applying filtering.

Thanks

Thank you very much, Bernie.

"Bernie Deitrick" wrote:

Alex,

Better to clear the formula, for the filtering to work correctly:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
Application.EnableEvents = False
With Target(1, 2)
If Target.Value < "" Then
.NumberFormat = "mmm"
.FormulaR1C1 = "=RC[-1]"
Else
.ClearContents
End If
End With
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP


"Alex" wrote in message
...
Thanks a lot again, Bernie. It's working perfectly.

How about if I want to have it empty if in the major (Date) column the data
has been deleted.
I'm trying this:
.FormulaR1C1 = "=IF(RC[-1]='','',RC[-1])" But, it's not working.

Thanks

"Bernie Deitrick" wrote:

Alex,

Copy the code below, then right-click on the worksheet tab and select "View Code". Paste the
code
in the window that appears.

This will only work (as written) when you enter values into one cell at a time.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
Application.EnableEvents = False
With Target(1, 2)
.NumberFormat = "mmm"
.FormulaR1C1 = "=RC[-1]"
End With
Application.EnableEvents = True
End Sub


"Alex" wrote in message
...
Thank you very much, Bernie.

The third one would be just exellent for me.

I have a data entry column for dates and that column with the formular that
just converts the date column into to show month. So, it's B column with =A2
in B2 formatted to show "mmm".

How could I do the third suggested item - enter the data into A2 and only
after that get a formula in B2?

Thanks,

Alex

"Bernie Deitrick" wrote:

Alex,

You could

1) put your formulas into a column that is not part of your table (separated
by at least one completely blank column), then just filter your table.

2) only enter your formula to match your data, then set Excel to Extend List
formats and formulas (Tools Options Edit tab - but only for Excel XP and
2003)

3) only enter your formula to match your data, and use a worksheet change
event to copy the formula when an entry is made in a new row.

HTH,
Bernie
MS Excel MVP


"Alex" wrote in message
...
I have a column with copied formular till row # 3,000. I've entered data
into
10 rows.
But, when I filter the data the next row for the data entries is 3,001.

How could I make the next empty row (11) available for the data entries
after applying filtering.

Thanks