Copy and sort timestamp date from noncontiguous rows to a column

Hi,

This one is going to need a sample of some sort.

S

A few questions:

1. How attached are you to circular referencing? I don't think I personally know how to accomplish what you're asking for without using a macro of some sort.
2. What happens if you update one of the numbers that you've already updated? Should the time update, does it count as two events in your list on the side? (That is, if you should accidentally type in a field you already had a number in?)
3. Do the fields that have numbers other than 1 have any relevance?
4. What other information do you need to go with the list on the side? The type of incident? The number you put in the cell?

That's all I can think of at the moment.

S

Hi,

Here's are two samples of what you can do. The first sample (Sample.xls) does the same as you have, but adds a macro to record your information in the far column. The second sample (Sample2.xls) removes the circular referencing and puts that part in the macro as well.

Note: You will need to have Macros enabled (I recommend Medium security so it prompts you when you open the file). You can review the code that is used by hitting ALT+F11 and double-clicking on Sheet10(DEC 5) in the top left small window. You can review the code the first time (disabling macros) and then if you're satisfied with the code, you can enable them the second time you open the file for it to work.

The changes that were made are as follows:

1. Added two columns at the beginning in columns B and C. The purpose of this is two-fold. First, it provides a way for the macro to identify that this is the proper row to record the event for. (If there's a value in the row for column C, then it's a valid row.) Second, it provides a simple way to identify what Type and Category belong to the event in the macro. These two columns can be hidden.

2. Added three columns on the far right to hold the data.

3. Added a macro that moves the information required to the Summary columns in the spreadsheet.

4. [Sample2.xls only] Removed the formulas for time and added that to the macro as well.

I recommend Sample2.xls, but either should work. Any questions about what the code actually does, feel free to ask. The code was set up so that hopefully you only have to make small changes should you need to adjust the spreadsheet layout. If you do make changes to which columns things are in, the macro will need to be adjusted somewhat.

S

Hmm, not sure. You did use the Sample2.xls one?

What else did you need to do to make it work for you?

S