Auto Populate New Sheet Based on Data from Multiple Other Sheets

Hello - new here,

I use Excel 2010.I've attached a copy of an example workbook. Backlog Queues - Example.xlsx

I am trying to create a worksheet that will display my "backlog" calls based on data from several other sheets within the same workbook. A call is backlogged if it hits a certain date without a first, second or third attempt being made on it. For example:

Date Exam # of Calls
4/21/15 MRI 0 Attempts
4/15/15 XR 2 Attempts
4/24/15 CT 1 Attempt

Etc... there are about 12 sheets (separated by exam type) that all have the same columns.

Now what I'd like the new worksheet to display is all of the rows that show 0 Attempts from 2+ days ago from today (would be auto changing the date), 1 Attempt from 5+ days ago from today & 2 Attempts from 8+ days ago from today.

From the above 3 fields, it should display the row for 4/21 & 4/15 but NOT 4/24 (based on today's date of 4/25).

Is this possible in Excel?? Any & all help is appreciated!

Backlog Queues - Example.xlsx

-Nate

Originally Posted by p24leclerc

This can probably be done with MACROS. It is OK for you?
Also, in your Backlog sheet, you have the column Title. But this colum does not exist on the other sheets.
What should be placed there?
Also, all columns of other sheets are present in your Backlog. Are you sure you give us the right sheets format?

Hi p24, thanks for your reply. I removed the other columns from the Coumadin & CT sheets but forgot to remove the request to only pull certain columns. Title can be removed from the backlog sheet also. Sorry for the confusion.

A macro would be fine, but I'm not very familiar with Visual Basic to write the code for it.

Is it possible to sort the original worksheets with the same macro before it pulls the rows to the "Backlog" sheet? When I divide the data up after the macro, I need to have it sorted by exam, then order priority (STAT/Emergency/ASAP/Routine) then # of calls (0/1/2). The sheets can be sorted manually first, but if a macro could do it all at once, it would save sorting 15 sheets separately.

If the macro cannot sort the other sheets before pulling the rows, I would like to see it sorted like this on the "Backlog":
-Procedure
-Order Priority
-# of Calls
-Order Date

Thanks again for your help!

Looks good to me! I really appreciate the help Pierre!

Pierre,
When adding other worksheets & more data, it appears to cause a problem with the "Backlog" sheet. Here is a new version with the added sheets. When you hit "Create Backlog" it causes several blank rows with the "Exam" column filled in still based on the other worksheets that have no data. This also has the added columns that I had removed previously, which causes it to pull everything to the backlog sheet, but replaces the "Title" column data with the Exam that you created (I don't need anything beyond the "Notes" column to come to the backlog . Is there a way to only pull the columns listed on the backlog worksheet & ensure that the sheets with no applicable data don't create blank rows?

Backlog Tracker.xlsm

Thanks - Nate

It works great Pierre, thank you very much!!

Now to add to another step in it, is it possible to create another button that could clear all subsequent pages besides backlog?

How about automatically creating a pivot table to sort by the number of 0, 1, & 2s by exam in a daily table?

Just throwing out thoughts to see if things are even possible

Pierre,
I have tried to use this one & it does correctly create the backlog & clears the other sheets however when I push "Create Backlog" button, it pops up with Run-time error 1004 & when I click debug it highlights this row:

Sheets("Pivot").PivotTables("PivotTable3").PivotCache.Refresh

The pivot table does not populate with data. If you'd like to use the previous example workbooks with the data to test it out & see what may be happening, that would be great!

Not sure what happened but I just tried it again & it seems to be working fine! I have another project I am going to be working on involving some of the same data, do you think you could assist with this new one also? I can post it in here.