I'm creating a simple Excel spreadsheet for table data entry (table created using the "Format as table" option — couple of columns, there could be hundred of rows). I would like to always have the last row of the table blank and ready for data entry (otherwise I don't see the dropdowns). I am pretty sure I have seen that in the past, but I can't replicate. Any idea how I could do that? Ideally without VBA.
Excel – Table data entry – How to always have a blank row at the end of an Excel table
data validationmicrosoft excelmicrosoft-excel-2007
Related Solutions
Here's a pure Excel solution with no VBA. It works by using an INDEX function to step down the rows and across the columns of the SQL data until the values are exhausted and an error condition results. An IFERROR function catches the error and uses a second INDEX function to step down the rows and across the columns of the manually entered data, again until those values are exhausted and an error condition results. A second IFERROR function catches the error and returns a dash ("-"). (The SQL data must be refreshed via the Ribbon for the formulas to produce a correct result.)
Create a dynamic named range SQLDB for the SQL data in Sheet1 using the formula:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1))
Create a second dynamic named range EXCELRNG for the manually entered data in Sheet2 using the formula:
=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet2!$1:$1))
Both of these named ranges assume that variable names are entered in row 1 of each of the two sheets.
Enter the variable names in row 1 of Sheet3 (beginning in cell A1).
Enter the following formula In cell A2 of Sheet3:
=IFERROR(INDEX(SQLDB,ROWS(A$2:A2),COLUMN(A2)),IFERROR(INDEX(EXCELRNG,ROWS(A$2:A2)-ROWS(SQLDB),COLUMN(A2)),"-"))
Copy the formula across the variable name columns, and then down the rows until the results of the formulas are all dashes ("-").
It is possible as a next step to create a Pivot Table in another sheet for analysis and organization.
Again the first step would be to create a dynamic named range, say, RESULTRNG, inserting the following formula in the Name Manager input box for the named range:
=OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet1!$A:$A)+COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet1!$1:$1))
Then create a Pivot Table in a new sheet, setting RESULTRNG as the table you want to analyze. This will filter out any trailing dashes from the formula table in Sheet3.
This works because the RESULTRNG formula counts up the total number of rows in Sheet1 and Sheet2 (excluding the header in Sheet2) and the total number of columns in Sheet1, and sets its extent based on those counts, excluding any dashes in any trailing rows (or columns) in the Sheet3 formula table.
Rather than using the entire worksheet as the source of the pivot table data, use a dynamic range. This will expand and contract as you add and remove data. You will just need to refresh your pivot table each time you change the data. This assumes that you have all your data at the top of the worksheet and are adding new data to the bottom, so the range will expand downwards.
In Excel 2010, go to the Formulas tab and select Name Manager. Create a New range, call it something like 'all_data' (spaces aren't allowed in the name). In the 'Refers to' box, use the following formula, adapted for your own data:
=OFFSET(Source!$A$1,0,0,COUNTA(Source!$A:$A),1)
To break this down:
Source!$A$1 reference - this is usually the top left cell of your data (usually the first cell in the header row)
0,0 rows,cols - you don't want to offset from the reference so these are both zero
COUNTA(Source!$A:$A) height - this will count the number of non-blank cells in column A - change this to a column that will always have an entry for each row, for example the column that has an ID for each row
1 width - this is the number of columns across your data is - e.g. if you have columns A to E filled then this number would be 5
When you insert a new pivot table, type the named range (all_data) in the 'Table/Range' box, rather than selecting the entire worksheet.
Best Answer
It's often the simplest method to extend the size of your table in advance for as many rows as you think you'll need. Sorting and formulas will continue to work as expected.
Alternatively, the manual way is to select the last row in your table and do "Insert row below" (or use the little arrow in the bottom right cell). This could be automated using VBA if you're so inclined, although I usually find extending the table does the job just as well and makes it easier for the end-user to deal with.