|
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" 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 |
| All times are GMT +1. The time now is 08:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com