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
51 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 21

Expert Comment

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

Author Comment

by:Marcia Morris
ID: 41755899
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
ID: 41758791
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
ID: 41758842
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 21

Expert Comment

by:Ejgil Hedegaard
ID: 41759905
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Marcia Morris
ID: 41761641
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 21

Expert Comment

by:Ejgil Hedegaard
ID: 41762729
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
ID: 41762851
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 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 41762895
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
ID: 41767632
Ejgil you have been so helpful. The Sales Admin doing a final check.
0
 

Author Closing Comment

by:Marcia Morris
ID: 41771797
Ejgil you were so helpful! Thank you!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

943 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

7 Experts available now in Live!

Get 1:1 Help Now