Data Consolidation

hlaur

New member
Joined
Feb 4, 2019
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
This is my first post, so be patient with me.

I currently have a workbook containing a worksheet for each employee, and a table in each one including: a list of procedures that each employee must be tested on, the date the the testing was completed, and the date that the employee must be tested again.
In each worksheet, I have used a date function to add 5 years to the completed date, to create the due date.
Then, in the due date column, I have used conditional formatting to change the color of the cell to yellow and red if the due date is within six months and has already passed, respectively.

On a separate worksheet, I have a list of the employee names. I have used an if function and sum function to get a column to state "update required" if the due date is within six months, or already passed.

Is there any way to also get excel to report the procedure number and due date of the expired procedure in this worksheet?

Somehow, I'd like to summarize on one separate worksheet if any updates are required, and if so, which procedures require updates by what time.
 
Attach a sample workbook
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.


Remember to desensitize the data.
 
Attach a sample workbook
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.


Remember to desensitize the data.

I've attached a very simple version of what I'm aiming to achieve. I have manually entered in the procedure number and due date of the required update. I'm hoping to find a way to do this automatically. Hope this helps.
 

Attachments

  • procedure update sample.xlsx
    11 KB · Views: 9
I've attached a very simple version
I did not fully understand, but try this idea.
I suppose you did not consider all the possible situations.
My assumption is that in a workbook you have multiple worksheets that have the name of a worker.
Is there a possibility that more conditions (multiple number 1) be filled in the range of F2:F5?

1st formula Testing status
Code:
=IF(INDIRECT("'"&A2&"'!F6")>0;"UPDATE REQUIRED";"")

2nd formula Procedure Requiring Update
Code:
=INDEX(INDIRECT("'"&A2&"'!A$2:A$5");MATCH(1;INDIRECT("'"&A2&"'!$F$2:$F$5");0))

3rd formula Due Date of Procedure
Code:
=VLOOKUP(C2;INDIRECT("'"&A2&"'!$A$2:$C$5");3;FALSE)

In the D1 cell, there is a formula that returns the name of the worksheet
 

Attachments

  • hlaur-navic9764.xlsx
    16.1 KB · Views: 4
I am fairly new to excel and am still learning, so thanks for your patience!
This works out quite well. However, an issue arises when multiple procedures need updating.

How might I report multiple procedure numbers and dates if there is more than one (number one) in F2:F5?
 
How might I report multiple procedure numbers and dates if there is more than one
Today I had too much time, so I did something.

In the workbook you have auxiliary worksheets, named 'Index'.
This worksheet serves as a template, it contains all the data from other worksheets (I set 20 rows for each worksheet). It also contains a list of all the worksheets in the active workbook. For this list of all worksheets I used VBA.

See all comments and formulas well. You see, there is the ARRAY formula.
I used Data Validation to choose the person you want results for.

When you create a new worksheet, you need to add it to the Sheet List by clicking on the VBA button. Excel will automatically pull all the data on the extra worksheet Index.

If this can help you, that's OK. But if this does not help you, I'm sorry I give up.
 

Attachments

  • hlaur-navic9764-2.xlsm
    59.1 KB · Views: 7
Back
Top