My subject line may be a poor description, as I'm struggling to explain this problem and behavior. I hope the screenshots will assist.
I have a workbook that will contain Names, Accounts, Effective Dates, and then extract a specific Month from the aforementioned effective date. A filter will be applied to these headers so users can sort, such as for a specific person's name or a particular month.
I'm using an IF statement to extract the Month. It's programmed to return a blank value if no data (Name, Account, Effective Date) have been input. The formula for the month column will go on for 1000 rows, as this workbook is intended to be used for a full calendar year, with data being added all throughout the year.
The problem I'm encountering is that when a Filter is applied for Name (or any other field), the results "jump" down to the next empty row.
So in my screenshot, I have the Month field programmed until row 15.
If the user sorts for "John", the results jump down to display Row 7 and then Row 20 as the next available line for data entry.
I know the user could sort for "John" + "(Blanks)", but they never do this. If the users don't clear the filters, the result is data jumping around on the workbook. It gets keyed in on random rows, rather than consecutive rows throughout the year.
I also know that Excel may count my "blank" month field as a "1" value, even if it's not displayed. It's kind of a binary yes/no, 1/0, situation behind the scenes. But I don't want that to happen. I've tried TEXT, MONTH, etc. to solve the problem. I've even given up and had users manually key in the month, which solves the sort problem (since Excel doesn't detect my formula in rows later in the workbook), but I would love to automate this functionality. There has been too much data garbage if it's not automatic (users may input "10" vs. "Oct" vs "October"...ugh).
Is there a way to change the filter to work better?
Or a way to change how the Month is extracted, so it doesn't mess up the filters?
Thank you in advance!
I have a workbook that will contain Names, Accounts, Effective Dates, and then extract a specific Month from the aforementioned effective date. A filter will be applied to these headers so users can sort, such as for a specific person's name or a particular month.
I'm using an IF statement to extract the Month. It's programmed to return a blank value if no data (Name, Account, Effective Date) have been input. The formula for the month column will go on for 1000 rows, as this workbook is intended to be used for a full calendar year, with data being added all throughout the year.
The problem I'm encountering is that when a Filter is applied for Name (or any other field), the results "jump" down to the next empty row.
So in my screenshot, I have the Month field programmed until row 15.
If the user sorts for "John", the results jump down to display Row 7 and then Row 20 as the next available line for data entry.
I know the user could sort for "John" + "(Blanks)", but they never do this. If the users don't clear the filters, the result is data jumping around on the workbook. It gets keyed in on random rows, rather than consecutive rows throughout the year.
I also know that Excel may count my "blank" month field as a "1" value, even if it's not displayed. It's kind of a binary yes/no, 1/0, situation behind the scenes. But I don't want that to happen. I've tried TEXT, MONTH, etc. to solve the problem. I've even given up and had users manually key in the month, which solves the sort problem (since Excel doesn't detect my formula in rows later in the workbook), but I would love to automate this functionality. There has been too much data garbage if it's not automatic (users may input "10" vs. "Oct" vs "October"...ugh).
Is there a way to change the filter to work better?
Or a way to change how the Month is extracted, so it doesn't mess up the filters?
Thank you in advance!
