Automatic data transferral between different sheets

[his5r2m]

Hello,

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”).

I’ve attached a small section from my spreadsheet with the details of one of the nine departments and the expiration summary.

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).

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

[arthurbr]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[his5r2m]

I tried to attach a dummy workbook but struggled to adhere to the size restrictions

[arthurbr]

You can eventually zip the files

[his5r2m]

okay thanks for the advice arthurbr. Ive attached a zip file containing a dummy workbook.

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.

Is there a way can make this transfer of data automatic?

Many thanks,

Richard

[his5r2m]

Anyone fancy a challenge today?!

[mayberce]

I'm watching this thread very closely because I have what seems to be a simillar variation of the problem. I too have multiple sheets and one summary sheet. But where I think my porblem deviates from this thread is that each sheet establishies an ODBC connection to my SQL server and executes one stored procedure.
1 sheet = the resulting value of 1 stored procedure.

I then wrote a macro to copy the values from each sheet into my summary sheet. However, the real pain in this approach is that I have to manually kick off the code that pulls the data into each sheet. In other words , 10 sheets means I have to manually execute 10 sub()'s and then execute my macro to copy it all into my summary sheet.

So here I am hoping that there is some way for each sheet to automatically run its respective sub() and pull fresh data each time that I launch the spreadsheet. This way I would only have to execute the summary sheet macro instead of having to manually execute everything.

If this is the wrong thread, I'm extrememly sorry for posting.

Chris

[mayberce]

I think I got it!

The problem was in my WITH statement. I replaced 'ActiveSheet' with the actual sheet I wanted to work with (called 'Sheet1') and added Sheet1 to destination:= part as well.

--old code--
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)

--new code--

With Sheet1.QueryTables.Add(Connection:=connstring,
Destination:=Sheet1.Range("A1"), Sql:=sqlstring)

[his5r2m]

a bit over my head I'm afraid Chris! Appreciate the effort though, thanks