Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
57 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 23

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 23

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 23

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 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.

688 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