I have a macro inserting a new row and providing a numbering sequence that works with our number of jobs in a list of jobs.
I have had to set these jobs up in a typical table with the following columns:
Job Number
Project Name
PM
Bid Date
Probability
Contract Amount
I have some formulas on a separate worksheet evaluating this tables data for statistics automatically. When setting up those formulas I set them up as a range including only the data, not the table headers because as I ran my macro to introduce a new line at the top of the data (just below the headers) my statistics formulas would bump down one line and not include the newly inserted line.
To solve that problem I introduced a blank line directly below my column headers. Therefore I could keep that blank line as part of my range of statistics formulas, therefore expanding the range of these formulas as new lines are introduced, rather than it bumping down. (Example is the formula is normally looking at row 6, but when I ran the macro which inserts a new row at row 6, it change the range on all my statistics formulas to row7, rather than expanding the range.)
The new problem is that now I am losing quick filtering because of this blank line. I believe this function needs to have clearly defined headers with no information above? How does it know where the top of my table is?
I need to be able to introduce new rows and have that new row included in my statistics formulas, but I also need to have the filtering capabilities. Its a bit of a catch-22. Does anyone know of an easy solution for this? Maybe I have laid things out a bit foolish? My tables are summarizing at the top because summarizing at the bottom was problematic as the list gets quite long and scrolling down was not friendly.
I have had to set these jobs up in a typical table with the following columns:
Job Number
Project Name
PM
Bid Date
Probability
Contract Amount
I have some formulas on a separate worksheet evaluating this tables data for statistics automatically. When setting up those formulas I set them up as a range including only the data, not the table headers because as I ran my macro to introduce a new line at the top of the data (just below the headers) my statistics formulas would bump down one line and not include the newly inserted line.
To solve that problem I introduced a blank line directly below my column headers. Therefore I could keep that blank line as part of my range of statistics formulas, therefore expanding the range of these formulas as new lines are introduced, rather than it bumping down. (Example is the formula is normally looking at row 6, but when I ran the macro which inserts a new row at row 6, it change the range on all my statistics formulas to row7, rather than expanding the range.)
The new problem is that now I am losing quick filtering because of this blank line. I believe this function needs to have clearly defined headers with no information above? How does it know where the top of my table is?
I need to be able to introduce new rows and have that new row included in my statistics formulas, but I also need to have the filtering capabilities. Its a bit of a catch-22. Does anyone know of an easy solution for this? Maybe I have laid things out a bit foolish? My tables are summarizing at the top because summarizing at the bottom was problematic as the list gets quite long and scrolling down was not friendly.
