Solved

Dynamic CountIfs and Data copy

Posted on 2014-10-17
4
290 Views
Last Modified: 2014-10-17
Hello,

I have written formulas that require manual intervention and entry and my company doesn't like them.  They want them automated and to change frequently.

This seems fairly complicated but hopefully only to me!  I inherited this sheet so if you know of a better way to do this, please suggest!

What I am required to do is:
1.  I pull over data from TFS - through a query in TFS.  This goes onto one sheet called 'TFS Data'
2.  I create a pivot table based on that data and place it on a sheet titled 'Metrics'.  I pull over only 2 columns of the data.
     a.  The pivot table has two columns:  Product and ID.  
           The IDs are grouped in the applicable Product.  
            For instance; one Product is called Accounting Export.  This section contains 3 IDs:  1100, 1101, and 1102.  
     (This could change.  I could pull information over and it be Accounting Export with 4 IDs or no Accounting Export section at all).
3.  A sheet will also be created titled 'Accounting Export'.  This sheet will have a column that contains the IDs (1100, 1101, 1102)
4.  I want to place formulas in the columns next to each ID.  The formula will look on the accounting export page to get the count of rows that have that ID in column A. It will fill in this formula for every ID in the pivot table.

I've attached a file that will hopefully explain it well.
exMetrics.xlsx
0
Comment
Question by:Katharina DuBose
[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
  • 2
4 Comments
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 300 total points
ID: 40387475
As an intermediate step - and to make sure I understand your request - see the attached example file showing a modification of your formulas on the Metrics sheet.  I changed a few of your percentage formulas also to test the denominator in every case so you wouldn't see #DIV/0! errors.

-Glenn
EE-exMetrics.xlsx
0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 200 total points
ID: 40387476
Hello,

as i understood you need formulas just in the Metric sheet to get the relevant values. I added just one column in the TFS data sheet to make it more searchable...

Can you take a look if it is acceptable to you and give comments if not?
exMetrics.xlsx
0
 

Author Comment

by:Katharina DuBose
ID: 40387490
These both work for my needs.  Samo4fun, you blew my mind.  I have to think on how that can work for me with the other steps I need and study it to learn what it does with the benefits.  I didn't even imagine combining the two.

Thank you both very much!
0
 

Author Closing Comment

by:Katharina DuBose
ID: 40387497
Both solutions worked for my needs and question.  I gave more points to the first one that came in.  Thank you for being so thorough and giving me the example to view.  It was clear and I can learn from it.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

710 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