Solved

How can I extract data from one tab on a specific individuals to numerous tabs in Excel--PART 2?

Posted on 2016-08-11
11
46 Views
Last Modified: 2016-08-26
I had this question after viewing How can I extract data from one tab on a specific individuals to numerous tabs in Excel?.

Sorry, my question got closed because I took to long to respond and I can't figure out how to reopen. Below is my new question.

Ejgil..The Sales Admin reviewed the spreadsheet and asked for some additional items that she was also doing manually.
  1. I was trying to include a conditional format to make the entire row of of each tab highlight yellow if it contains the text "large package". I don't know what I am not doing correct in the 'total' tab, but it only highlighted the cell with the words large package.
  2. Is it possible to identify all line items in the "Total" tab with a Bid Due Date (column I) of {3 weeks ago from today → yesterday’s date} Example: Today is 8/11, so the late range would be 7/20 – 8/10, and extract these to the "Late Tab"? They already show up in each Estimator tab but they want to see what's late in one tab. Basically every day they look at what's late from the system they export the data out of.
RFQ_Status_Report_TEMPLATE_MM_TEST.xlsx
0
Comment
Question by:Marcia Morris
  • 7
  • 4
11 Comments
 
LVL 20

Expert Comment

by:Ejgil Hedegaard
Comment Utility
Here is the file again with the revisions
RFQ-Status-Report-TEMPLATE.xlsx
0
 

Author Comment

by:Marcia Morris
Comment Utility
Ejgil...thank you again. I will review again with the Sales Admin and see if she has any other requests.
0
 

Author Comment

by:Marcia Morris
Comment Utility
Ejgil...I want to understand how to read the formula #2 below. What exactly is it saying? Trying to get better at reading formula's.

1. Columns A:N highlighted for rows containing "Large Package" in column E.

2. Formula in column O on Total identify the late rows, setting an increasing number to use on the Late tab. - =IF(TODAY()-I14>21,MAX($O$12:O13)+1,0)
0
 

Author Comment

by:Marcia Morris
Comment Utility
Ejgil...I don't know what I did wrong. I was testing the file with new data and column O in the "Total" tab is showing all zeros' and it's not pulling the data into the late file. Once I work through all the kinks, I will be locking it so no-one messes up the formula's.
RFQ_Status_Report_TEMPLATE_081616.xlsx
0
 
LVL 20

Expert Comment

by:Ejgil Hedegaard
Comment Utility
None is late, so all will be zeros.
The formula only looked at the active range, so I have changed to this.
=IF(AND(A13<>"",TODAY()-I13>21),MAX($O$12:O12)+1,0)
For now it is set to 2000 rows.
I have changed the index formulas on the engineer sheets to look at the entire Total sheet, instead of just the data range.
It does not matter for the calculation speed, but ensures that new data always will be found.
The array formulas setting the row specifications for Index changed to normal formulas, using another principle.
RFQ_Status_Report_TEMPLATE_081616.xlsx
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Marcia Morris
Comment Utility
Hi Ejgil...After speaking again with the Sales Admin. She already filters the data by three week date range before exporting it out of the system. So basically all I need is the formula to pull the data from the "bid due date" (column I) field that is late based on today's date.

=IF(AND(A13<>"",TODAY()-I13>21),MAX($O$12:O12)+1,0)
RFQ_Status_Report_TEMPLATE_081816.xlsx
0
 
LVL 20

Expert Comment

by:Ejgil Hedegaard
Comment Utility
I don't understand what you mean.
The formula find the late rows where "bid due date" is before 3 weeks ago.
None is late, so none is found.
Please explain.
0
 

Author Comment

by:Marcia Morris
Comment Utility
Ejgil,

Sorry as I'm not explaining it correctly. It would be any bids that are late as of today. Since today is 8/19 anything that was due yesterday and prior is considered late.
0
 
LVL 20

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
Comment Utility
Replace the formula in O10 with this, and copy down.
=IF(AND(A10<>"",TODAY()>I10),MAX($O$9:O9)+1,0)
See file
RFQ_Status_Report_TEMPLATE_081816.xlsx
0
 

Author Comment

by:Marcia Morris
Comment Utility
Ejgil you have been so helpful. The Sales Admin doing a final check.
0
 

Author Closing Comment

by:Marcia Morris
Comment Utility
Ejgil you were so helpful! Thank you!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now