VBA macro sheet references and formulas

[mamachrissy1028]

2 part question here.Looking for a little help here. I have very basic VBA skills. I am running a macro that copies and pastes data from multiple spreadsheets into a "Output" spreadsheet based on certain criteria.

1.While running it also pulls the sheet name that the information was pulled from. What i am looking for is another sub that will then pull the date which is in cell a1 of each sheet if the sheet name matches what is in each non-blank cell in column a on my output tab.... I have searched and searched to try to get this but cannot seem to figure it out.

2. in the cells in column c i would like to insert and index match formula or command that will look for the person in the adjacent cell in column b find them on a specified tab and return their group name

Thank you in advance for taking the time to help me out!
Also for some reason i am not able to upload and attach my spreadsheet so here is snippet of what it looks like... Attachment 492706

[jomili]

A picture is worth a thousand words. If you'll post your workbook (or a stripped down example) it will really help with trying to solve the problems.

[mamachrissy1028]

[attach]forum examples.xls[attach]