Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

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

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
Marcia Morris
Asked:
Marcia Morris
  • 7
  • 4
1 Solution
 
Ejgil HedegaardCommented:
Here is the file again with the revisions
RFQ-Status-Report-TEMPLATE.xlsx
0
 
Marcia MorrisProject ManagerAuthor Commented:
Ejgil...thank you again. I will review again with the Sales Admin and see if she has any other requests.
0
 
Marcia MorrisProject ManagerAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Marcia MorrisProject ManagerAuthor Commented:
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
 
Ejgil HedegaardCommented:
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
 
Marcia MorrisProject ManagerAuthor Commented:
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
 
Ejgil HedegaardCommented:
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
 
Marcia MorrisProject ManagerAuthor Commented:
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
 
Ejgil HedegaardCommented:
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
 
Marcia MorrisProject ManagerAuthor Commented:
Ejgil you have been so helpful. The Sales Admin doing a final check.
0
 
Marcia MorrisProject ManagerAuthor Commented:
Ejgil you were so helpful! Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now