Hello,
I am working on a data input system that copies data from several worksheets and stores it in a separate database style spreadsheet. Column A in each of the sheets is an ID column which is simply an incremented integer starting with 1. I use the number of rows in column A that have IDs to determine how many rows need to be copied. I was manually entering the IDs but if I forgot to enter one, then the data wouldn't get copied. I thought I could use a formula to accomplish an auto-number, but it seems a formula is considered the same as a value when it comes to determining the last used row. Each worksheet has a different number of columns but will have a maximum of 20 rows of data. I am thinking that I should be able to accomplish what I want to do with VBA code that fires when a change event in the row of interest occurs. Row 1 is a header row so the auto-numbers would be needed in rows 2-21 with their values being (row number - 1). My question is how do I get a single cell to populate with an auto-number (row #-1) when any other cell in that row (within a specific range of columns) becomes non-empty and then changes back to null if all data in that row is subsequently deleted. Thanks in advance for any thoughts or help on this.
I am working on a data input system that copies data from several worksheets and stores it in a separate database style spreadsheet. Column A in each of the sheets is an ID column which is simply an incremented integer starting with 1. I use the number of rows in column A that have IDs to determine how many rows need to be copied. I was manually entering the IDs but if I forgot to enter one, then the data wouldn't get copied. I thought I could use a formula to accomplish an auto-number, but it seems a formula is considered the same as a value when it comes to determining the last used row. Each worksheet has a different number of columns but will have a maximum of 20 rows of data. I am thinking that I should be able to accomplish what I want to do with VBA code that fires when a change event in the row of interest occurs. Row 1 is a header row so the auto-numbers would be needed in rows 2-21 with their values being (row number - 1). My question is how do I get a single cell to populate with an auto-number (row #-1) when any other cell in that row (within a specific range of columns) becomes non-empty and then changes back to null if all data in that row is subsequently deleted. Thanks in advance for any thoughts or help on this.