filtering data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

:

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


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
Click to expand...
Click to expand...
Click to expand...
 
Thank you very much, Bernie.

Bernie Deitrick said:
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 said:
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 said:
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


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

:

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


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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top