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
53 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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
 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

856 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