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.