Search results

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

In your formula you have set the part ROW(all!$A3) with a relative reference, but it should be absolute reference... i.e. ROW(all!$A$3). Try this revised formula. Remember to confirm with CSE keys before copying down...

Your dates start in A1, but you don't include A1 in your formula ranges. Is that how it should be? Try revising the formula to include A1 and let me know if it corrects the problem. If it doesn't, please attach an excel spreadsheet sample and show the expected results with explanation.

You must confirm the formula with CTRL+SHIFT+ENTER not just ENTER (you should see curly brackets appear around the formula), then copy it down. It is an Array Formula.

Using formulas.... Assuming your first time is in A1 and your second is in B1 and the sum in C1 You can convert those times in A2 and B2 with formula in A2: =A1/(60*60*24) or =A1/86400 copied across. Then format these cells as Number: Custom with Type: [hh]:mm:ss You can also just get...

If you mean the path, workbook name and sheet name or any combination thereof is stored in specific cells in the sheet and you want to reference those to get the info required, then you can employ the INDIRECT function. e.g. this assumes the path (including the drive letter and the...