Recent content by NBVC

Try: =IFNA(VLOOKUP(E2,Range-A,5,FALSE),IFNA(VLOOKUP(E2,Range-B,3,FALSE),""))

or you can use custom format to hide 0's.. Format Cells|Custom|0;-0;;@

You can use =SUM() to let you know if all 4 cells are blank... e.g. =IF(SUM(B2,C2,F2,G2)=0,"",((F2+G2)-(B2+C2))*24)

Is the Supervisor also listed in column ApprovedAmounts!$A$2:$A$16 as employees with limits? Assuming yes and Assuming Employee enters his name in A2, Then =IF(VLOOKUP(A2,ApprovedAmounts!$A$2:$B$16,2,0)<=J26,"office manager to input into system and...

I don't see that error in your attachment, but I did notice that your formula has an invalid reference identified with the #REF! error... =SUMIFS('8 day average'!$B$3:$B$19,'8 day average'!$A$3:$A$19,">="&'8 day average'!$A3-8,'8 day average'!$A$3:$A$19,"<="&'8 day...

Explaining is always the hardest part....:eek: The idea is to assign a value to every row, including the spaces in between teams, so that way we can sort by that row since you only have those total scores in one row for each group of 4 rows. Ok, let's go from inside out... This -...

hmmm... it didn't for me. Did you start the formula in R8? Did you select from C8 down to R107 for sorting? The formula above got some extra spaces due to forum... maybe it didn't work properly... try again with: =IF(AND($C8="",$C9=""),999...

Ok, try this. Assuming you want to sort in Descending order from Highest score to Lowest, then: In R8 (i.e. row between column headers and first set of scores) enter formula: =IF(AND($C8="",$C9=""),-999, IFERROR(INDEX($O8:$O$107,MATCH(TRUE,INDEX(ISNUMBER($O8:$O$107),0),0)),-999)) copied...

In a new column at row 9 (e.g R9) enter formula: =IF(O9="","",RANK(O9,$O$9:$O$107)) copied down. Is that what you need?

If you are using Named Ranges, then generally you would not need to reference the worksheet also... (unless you changed the scope of then named range from the default Workbook)

As mentioned by CJ_London, you need to make the reference ranges absolute if you don't want them to change, by adding the $ before the column and/or row references. Also, instead of hard-coding the dates and times, you can reference them in the formula, using partial absoluting (i.e. only apply...

I think the LEFT() should be around the whole references e.g. If Left(store, 4) = Left(Workbooks("whitespace (aggregated) jun 28.xlsx").Worksheets("existing stores detail").Range("C" & x), 4) Then

How about? http://peltiertech.com/pivot-chart-formatting-changes/ or http://www.contextures.com/xlfaqPivot.html#ChartFormat

Does this help? http://datapigtechnologies.com/blog/index.php/three-tips-for-making-your-pivot-table-formatting-stick/comment-page-1/

Excel see the formula result null strings as text strings and still counts them. You can add another column with a formula like: =IF(A2="",0,1) Then Count or Sum that column in your pivot table