automatic transfer into new row (impossible question??)

[his5r2m]

Hello,

I've been on several excel help forums now but so far my problem has proved unanswerable. I'm starting to think that maybe what I want to do is impossible!

I am currently compiling a training matrix which records the start and expiry dates of various training certificates. I have 10 sheets within my workbook separating employees into the departments they work in (e.g. asbestos, electrical, gas, admin etc). My final tab is an ‘expiration summary’ which shows the remaining number of days a training certificate is valid for: (“expiry date” – “today()” = “remaining validity”).


What I would ideally like to do, if it’s possible, is to make the process of transferring information from the department tabs to the ‘expiration summary’ automatic. If, for example, an asbestos operative completes a new training course, “new training”, which expires on 1/1/2010, and thus a new column is added to the asbestos department sheet, what I would like to happen is for this information be automatically transfer to the ‘expiration summary’ sheet, along with the employee’s details (department, pay number and name).

I’ve attached a small section from my spreadsheet with the details of one of the nine departments and the expiration summary. The 'lifts' sheet is an example of 1 of the 9 department tabs. The yellow column represents new data being entered.
On the second sheet, "expiry summary", is where I would like the new data, represented again by the yellow section, to transfer to.

Similarly, if someone finds out that a training certificate that I have listed as having an unknown expiry date is in fact valid for 3 years, and so replaces the ‘unknowns’ in the column with the correct expiry dates, is there some kind of formula that will recognise that a date has been entered and so add the details of the employee and training certificate in a new row in the ‘expiry summary’?

Is this possible?

Many thanks,

Richard

[mhuston]

As a start, you may want to look at: http://www.accountingweb.co.uk/cgi-b....cgi?id=136372
There is code, that I have used, that compiles data from similar worksheets (tabs) onto a summary sheet. As for "automatic", something would have to trigger the transfer based upon a change in an event. The easiest would be to have it done at worksheet open; otherwise you could run a macro manually.