Macro Help

[Jason Link]

I am trying to write a macro in order to do the following:

In one workbook, I want to search for data from another workbook and return a value based on the ID # if certain criteria is met.
IE: If it says Melanie Jackson or Hector Hernandez in the October column, I want it to return this value (Melanie Jackson or Hector Hernandez October). If it says no review, I want the macro to look to September and return what I just mentioned. I basically want to keep track of the last time something was reviewed and by whom from a master spreadsheet to individual spreadsheets. I don't know if this can even be done, but it seems like a complex thing of vlookups, IF statements and more. Thank you.


[TABLE="width: 297"]
<colgroup><col width="51" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1820;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3100;"> <col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3015;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3100;" span="2"> <tbody>[TR]
[TD="width: 51, bgcolor: black"]ID#[/TD]
[TD="width: 87, bgcolor: black"]July[/TD]
[TD="width: 85, bgcolor: black"]August[/TD]
[TD="width: 87, bgcolor: black"]September[/TD]
[TD="width: 87, bgcolor: black"]October[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2007[/TD]
[TD="bgcolor: transparent"]Melanie Jackson[/TD]
[TD="bgcolor: transparent"]No review[/TD]
[TD="bgcolor: transparent"]No review[/TD]
[TD="bgcolor: transparent"]Melanie Jackson[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2044[/TD]
[TD="bgcolor: transparent"]Melanie Jackson[/TD]
[TD="bgcolor: transparent"]No review[/TD]
[TD="bgcolor: transparent"]No review[/TD]
[TD="bgcolor: transparent"]Hector Hernandez[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2067[/TD]
[TD="bgcolor: transparent"]No Review[/TD]
[TD="bgcolor: transparent"]No review[/TD]
[TD="bgcolor: transparent"]Melanie Jackson[/TD]
[TD="bgcolor: transparent"]No review[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2070[/TD]
[TD="bgcolor: transparent"]Melanie Jackson[/TD]
[TD="bgcolor: transparent"]No review[/TD]
[TD="bgcolor: transparent"]No review[/TD]
[TD="bgcolor: transparent"]Melanie Jackson[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2111[/TD]
[TD="bgcolor: transparent"]Hector Hernandez[/TD]
[TD="bgcolor: transparent"]No review[/TD]
[TD="bgcolor: transparent"]No review[/TD]
[TD="bgcolor: transparent"]Melanie Jackson[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 297"]
<colgroup><col width="51" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1820;"><colgroup><col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3100;"><colgroup><col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3015;"><colgroup><col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3100;" span="2"><tbody>[TR]
[TD="width: 51, bgcolor: transparent, align: right"][/TD]
[TD="width: 87, bgcolor: transparent"][/TD]
[TD="width: 85, bgcolor: transparent"][/TD]
[TD="width: 87, bgcolor: transparent"][/TD]
[TD="width: 87, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[cliftonvilla]

This looks pretty easy to do, but your helpers may need a little more info - please confirm you mean workbooks and not worksheets. Do you wish to enter a single ID and return the single response (reviewer and month) or produce a list for all your IDs of the most recent reviewer and month? Presumably the list of months is ongoing to November, December etc? It's straightforward to reference a different worksheet or workbook from any worksheet and also straightforward to search backwards starting at the most recent month for the most recent reviewer with the review month.

 

[Jason Link]

I have a workbook for each month with several tabs (one for each ID #). The last worksheet of the workbook is a master worksheet, if you will, for that workbook, with information for each tab and I have formulas on each worksheet to pull information from the master worksheet. On the master worksheet, I want to pull information from this master workbook I created (the above example). I would like to run a macro/formula/something that will tell me the last time something was reviewed and by whom. Sorry, I know this might be too much information, but I'm trying to help get a sense of what the purpose is I am trying to do. From the original example, in September, I want to know that the last review for ID2007 was in July by Melanie Jackson. I guess to answer your question, I am trying to produce a list for all of the IDs and the most recent reviewer and month. Thank you for your help.

 

[cliftonvilla]

When coding this, I think I might have a list of all the workbook names in the final tab of each workbook, together with their full paths. These will be identical as the months go by, apart from the fact that the number of entries in each list goes up by each month to reflect the latest month. So September will include all the names of the workbooks from the very first one up to and including September. Then open each workbook in turn working from the most recent to the oldest (you can easily count how many workbook names are in the list). Place a marker against the most recent so you know which workbook to open first. You also need a list of all the IDs in this tab. In each workbook you open go to the tabs in turn which are named according to the ID, so visit tab 2007 or whatever ID is next in your list of IDs. Then parse each column to see when the last review was and the reviewer. Then report this to the most recent workbook, specifically to the worksheet named 2007.