filtering data

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> >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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>