Jason Link

New Member
Joined
Aug 27, 2018
Messages
2
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.


ID#JulyAugustSeptemberOctober
2007Melanie JacksonNo reviewNo reviewMelanie Jackson
2044Melanie JacksonNo reviewNo reviewHector Hernandez
2067No ReviewNo reviewMelanie JacksonNo review
2070Melanie JacksonNo reviewNo reviewMelanie Jackson
2111Hector HernandezNo reviewNo reviewMelanie Jackson
<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> </tbody>



<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>
</tbody>
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

cliftonvilla

New Member
Joined
Sep 21, 2017
Messages
7
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

New Member
Joined
Aug 27, 2018
Messages
2
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

New Member
Joined
Sep 21, 2017
Messages
7
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,500
Messages
5,529,222
Members
409,857
Latest member
KailuaTown
Top