Carim, you're amazing to keep going at it and I'm just not making what you're going at very clear. I greatly oversimplified the example attached in message #3. I apologize for my vagueness.
In my original file a new row of data entered in sheet 1 includes a date. New rows of data are not always entered sequentially according to the dates entered. Therefore the date entered in row 100 may be prior to the dates in the previous 10 rows. The next column in sheet 1 ranks the dates entered in all rows. The rank creates an index number for all data in that row. The rank may change depending on dates entered for data in new rows. If any row in sheet 1 contains data that is relevant to sheet 2, the formula in column A of sheet 2 assigns the current rank/index number from sheet 1 for that row of data. What occurs is, all data in sheet 2 (and other sheets) will always be sequential by date regardless of the sequence the data was entered in sheet 1.
I've attached a revised version of the message #3 sample file. The revised file includes both date and rank indexing. Dates in this example are non-sequential in sheet 1 to demonstrate the significance of the changing index number for sequencing data in other sheets. I've also set the formulas in sheet 1 columns D, E and F to key on the current rank/index number of the date for each row to demonstrate the dependency of all the data on the possibility of a changing index number. This should make it easier to understand why the formula in column 1 of other sheets must remain intact.
- Data is all manually entered in sheet 1 with the exception of the rank column and columns used for data assignment such as columns D, E and F in the example.
- The VBA code that I have can be modified to fill down formulas in sheet 1 based on the date entry so this sheet is not a problem.
- Data from previous entries may on occasion be changed and possibly including date.
- All data in sheets 2, 3 and 4 in the example must remain capable of changing with any change to data or change in ranking in sheet 1.
I have added a row of data in sheet 1 of the new example. It should be assigned to sheet 2 "X". In sheet 2 you will see that column A has picked up the current index number for the data but the code did not fill down the formulas for columns B,C and D as desired.
I hope I gave you what you need this time. Thanks for your tremendous patience.