If formula finds corresponding data, copies into table- how?

[BSWJON]

Hi Folks,

I am attempting to create an automated data grab type formula which I will then use to set up an automatic email macro.

What I'm looking to do is have a formula which looks through the table (table 2), finds any dates that are more than 2.5 years old (some sort of a if/find function using today()-912 I would presume) then locates the corresponding training course title in row two, and name in column C&D (it may be easier to concatenate these into one? Not sure.). Then copy this data into a table in another sheet (outstanding training) with said mined data.

So it would look at this:

and regurgitate this:

Has anyone done anything like this before? I'm afraid this is beyond the realms of my understanding...so any help appreciated!

Thanks all

 

[jasonb75]

First of all, we need to know if your dates are valid or not. Referring to the table in the top image, does =ISNUMBER(G7) return TRUE or FALSE?

 

[BSWJON]

Hi Jason and thanks for the assistance. It returns TRUE.

I just realised that a macro or something in VBA may be more applicable than just asking for a formula- sorry for being basic!

BTW just seen your signature line- I am using Excel 2013.

 

[jasonb75]

I just realised that a macro or something in VBA may be more applicable

I did think that after taking a second look at the image and your original post. Personally, I would do it with PQ, but that is something that I'm still getting to grips with on basics so not sure how to automate it.

A few more things to clarify before I start off on the wrong route.

Are there more columns with dates to the right of the image that should also be checked?
If yes, are there also columns to the right that should be excluded?

Are the dates manually entered or produced by existing formulas?

Should we assume that only rows with 'Yes' in column A should be included?

 

[BSWJON]

No idea what PQ even is..!

Yes lots more columns, up to AQ at present but this may/will increase as more courses are identified as being required. All of these columns should be included however it may be useful to know how to add a functionality that excludes courses that do not require refresher training.

Dates are manually entered.

Column A is actually redundant, I have moved all 'no' employees (ex employees) to another sheet for simplicitys' sake.

I may actually delete that now...

 

[jasonb75]

You might not even have PQ, I'm not entirely sure when it was added as a standard feature, although I believe that it can still be installed and used with some older versions.
If you do have it, it will be on the Data tab of the ribbon, look for 'Get Data' on the left side of the ribbon.

 

[BSWJON]

Nope, no signs of PQ here.

 

[jasonb75]

Keeping it as simple as possible for a first test, input Sheet1, output Sheet2, top left course date cell G3. These points will need to be edited in the code before you try to run it.

Assumed output sheet starts empty, nothing included for checking existing data there.

As always, use test data in order to prevent data loss in the event of problems.

Option Explicit
Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim c As Range, rng As Range, rw As Long
Set ws1 = Worksheets("sheet1"): Set ws2 = Worksheets("sheet2")
Set rng = ws1.Range("G2", ws1.Range("G2").SpecialCells(xlLastCell))
rw = 2
For Each c In rng.SpecialCells(xlConstants)
    If IsDate(c.Value) Then
        If Date > (c.Value + 912) Then
            ws2.Cells(rw, 1) = ws1.Cells(c.Row, 3) & " " & ws1.Cells(c.Row, 4)
            ws2.Cells(rw, 2) = ws1.Cells(2, c.Column)
            ws2.Cells(rw, 3) = c.Value
            rw = rw + 1
        End If
    End If
Next
End Sub

 

[BSWJON]

Thanks for this, you're going to have excuse my lack of knowledge here though. I don't understand what points need editing, it looks like you have the right info there?

I have popped this into VBA as follows:

Not got anywhere with it just yet though- does it need a button to make it fire? I have selected follow hyperlink for this reason and put a rectangle shape in to use as a button for the time being, assuming this is yes.

Thanks for your help!

 

[jasonb75]

I don't understand what points need editing,

The sheet names and the top left cell of the date range need editing, the bits in bold below. I've done the 2 in the top line based on the image above, if you deleted the 'Currently Employed' column and moved everything else to the right then G2 will probably need changing to F2.

Set ws1 = Worksheets("Summary"): Set ws2 = Worksheets("Outstanding Training")
Set rng = ws1.Range("G2", ws1.Range("G2").SpecialCells(xlLastCell))

To assign it to a button / shape, you just right click the shape and 'assign macro'. This must be a normally named macro in a standard module. Follow hyperlink is for hyperlinks in cells.
If you want it to run automatically it would be best done either when the file is opened or when it is saved.