[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Calculating Formula to show stats

Hi,

I want to pull data from the "Reviews" tab into the "Weekly" and "Monthly" rollups. I have formulars at the top, but, I can't get them to work. They have invalid references because I deleted some old sheets they were pointed at.

But, I can't figure out how to get them to work.


Thank you.
EEQuality-2-.xls
0
nomios
Asked:
nomios
  • 7
  • 6
  • 4
1 Solution
 
dlmilleCommented:
Well, your #REF! errors on the Monthly Rollup, Column A are errors, because you deleted a tab that this formula was referencing.  So, advise what sheet in the current attachment replaces an "old sheet", or revert back to your old sheet.  Let me know.

Same goes for Column G Weekly Rollup.

What were the old sheet names, and what names in your new workbook if any were the sheets to be used.

With that bit of info, we can get you back on track.

Dave
0
 
nomiosAuthor Commented:
It should reference the "associate team" sheet. It's just a list of names to pull up the manager who the employee reports to.

Thanks for helping, Dave.
0
 
gowflowCommented:
I tried but your file is full of missing link don't you have an old backup that could help me reconstruct ! its not important if the data is the same but at least I would try to undertand relation between tables.
gowflow
0
Independent Software Vendors: 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!

 
dlmilleCommented:
@nomios,

Select Row 5 to the bottom, column A in Monthly rollup.  Do a replace of #REF with 'Associate Team' and do replace all.  This will correct your reference errors.

Weekly rollup would do the same.  I didn't validate the formula further than that - is any data supposed to show up for any of these? I get no data results.  Has Associate Team sheet format changed?

Dave
0
 
dlmilleCommented:
I've done those replacements for you.  Your sheet has circular references and dummy data, I'm not sure I'm supposed to get any data results as a result of the "corrected" array formula, so you'll need to tell me if this solved your problem.

Cheers,

Dave
EEQuality-2-r1.xls
0
 
gowflowCommented:
you also get errors in sheet review in col Y and Z
you also get col Y and Z in Associate Review showing Name
and you have a circular refrence.
gowflow
0
 
nomiosAuthor Commented:
Well, let me rephrase. I'm not sure the formula is doing what I want.

The associate sheet simply references the manager/employee list. It has to use the "reviews" tab to grab the data to calculate the data.
0
 
gowflowCommented:
I am pretty sure that you deleted more that what you think you did. Again do you hv an old copy before you did the deletion even if it is way back ?
gowflow
0
 
nomiosAuthor Commented:
I do not, sorry.
0
 
gowflowCommented:
So to wrap it p you only deleted a sheet called Associate Team ? but it is there now !! did you recreate it ? are you missing maybe columns ? did you change its format ?
all this can help re connect
gowflow
0
 
nomiosAuthor Commented:
The original formula's looked a little like this:


Name:
=IF(ROWS('Associate Team'!$2:2)<=COUNTIF('Associate Team'!$C$2:$C$500,$P$2),INDEX('Associate Team'!$A$2:$A$500,SMALL(IF('Associate Team'!$C$2:$C$500=$P$2,ROW('Associate Team'!$C$2:$C$500)-ROW('Associate Team'!$C$2)+1),ROWS('Associate Team'!$2:2))),"")

Total Monitored:
=COUNTIFS(Reviews!$A:$A,'Monthly Rollup'!$A3,Reviews!$AB:$AB,$C$1)

Completed:
=IFERROR((COUNTIFS(Reviews!$A:$A,'Monthly Rollup'!$A3,Reviews!$B:$B,$I$3,Reviews!$AB:$AB,$C$1)/'Monthly Rollup'!$B3),0)

Incomplete:
=IFERROR((COUNTIFS(Reviews!$A:$A,'Monthly Rollup'!$A3,Reviews!$B:$B,$I$2,Reviews!$AB:$AB,$C$1)/'Monthly Rollup'!$B3),0)

Failed:
=IFERROR((COUNTIFS(Reviews!$A:$A,'Monthly Rollup'!$A3,Reviews!$B:$B,$I$4,Reviews!$AB:$AB,$C$1)/'Monthly Rollup'!$B3),0)

Manager:
=VLOOKUP(A3,'Associate Team'!A:B,2,0)

0
 
dlmilleCommented:
I see that - what is the Name: supposed to be grabbing - tell the story on how that's supposed to work?

There is not a column in "Associate Team" tab that looks at Failed% for example, so the part of the if statement having 'Associate Team'!$C$2:$C$500=$P$2 would not work

Dave
0
 
gowflowCommented:
That's the best I could reach
gowflow
EEQuality-2-NEW.xls
0
 
nomiosAuthor Commented:
I wish I could be more specific help to y'all. The latest file attached doesn't seem to be working (maybe I'm missing something?)

To try and clarify....when you go to the monthly sheet. I want to be able to enter the number of the month in cell B1. Then, have the sheet pull data from the "reviews" tab related to that month.

0
 
gowflowCommented:
That's what I thought and also had the same comment that nothing happens we followed ur instruction on the formulas. Maybe if you explain in plain english what each field means then we will build the formulas accordingly.
gowflow
0
 
nomiosAuthor Commented:
First off, let me say thanks for sticking with me through this to completion.

Maybe we should ignore the formula that I tried to use, and start from scratch with what I want to do.

In the "Monthly" tab, I want to enter the month in Cell "C1". Once this is done, I want to pull data from the "Reviews" tab that happened in that particular month.

I want to pull: The persons name, total amount of times they were monitored, how many were complete, incomplete, and failed. along with who their manager is.


0
 
gowflowCommented:
ok fine will take it one bitre at a time but now hv to go will log in later and will give it a try if no one else speed up on me and give you the solution
gowflow
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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