Veeam is happy to provide a free NFR license for one year. It allows for the nonâ€‘production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both onâ€‘premises and in the public cloud.

The formula "=MAX" returns every cell with the same date 23/5/2013

The Macro 'Macro1' removes the header in row 1

is this because I am using 2010?

0

JagwarmanAuthor Commented:

On further investigation the result in the formula box shows the correct answer of 17/5/2013 but the result shown in the spreadsheet is showing as 24/5/2013.

bizzare

0

JagwarmanAuthor Commented:

Have attached spreadsheet to assist and show what I mean. Hope someone can resolve this for me. Book3.xlsx

Hey,
In order to make it work the ranges cannot be full columns.
I have added 2 dynamic named ranges "Ref" and "Entry_Date" this will always bring back correct number of rows.

Please see the attached file for details. Let me know if you need any explanation. Book3.xlsx

Here are some user-defined functions that will return the maximum value in a range subject to a criteria. Install them in a regular module sheet, then use them with a worksheet formula like:
=MaxIf(A:A,A2,B:B) 'This is a regular formula

Function MaxIf(rgToCheck As Range, Criteria As Variant, rgToMax As Range) As Variant'Function tests rgToCheck for Criteria. Returns maximum of corresponding cells in rgToMax when rgToCheck cell satisfies Criteria.'rgToCheck is range to be tested against Criteria'Criteria may be a single value or an array of values'rgToMax is range from which the maximum value is selected'Function returns the maximum value if Criteria is a single value. It returns an array of maximums if Criteria is an array. _ =MaxIf(A1:A7, ">0",B1:B7) returns a single value _ =MaxIf(A1:A7, {">5", "<2"},B1:B7) returns a two column array of values _ =MaxIf(A1:A7, {">5"; "<2"},B1:B7) returns a two row array of values (semicolon instead of comma in array constant) _ =MaxIf(A1:A7, {">5", ">20"; "<2", "<1"}},B1:B7) returns a two column by two row array of valuesMaxIf = MinMaxIf(rgToCheck, Criteria, rgToMax, False)End FunctionPrivate Function MinMaxIf(rgToCheck As Range, Criteria As Variant, rgToMinMax As Range, bMinimum As Boolean) As Variant'Function tests rgToCheck for Criteria. Returns minimum/maximum of corresponding cells in rgToMinMax when rgToCheck cell satisfies Criteria.'rgToCheck is range to be tested against Criteria'Criteria may be a single value or an array of values'rgToMinMax is range from which the minimum/maximum value is selected'bMinimum is True when function returns a minimum value. It is False if a maximum value is returned.'Function returns the minimum/maximum value if Criteria is a single value. It returns an array of minimums/maximums if Criteria is an array.Dim i As Long, ii As Long, j As Long, jj As Long, n As Long, nCols As Long, nRows As Long, nnCols As Long, nnRows As LongDim d As DoubleDim iFirstCheck As Integer, iLastCheck As Integer, k As IntegerDim vCheck As Variant, vCriteria As Variant, vResults As VariantDim wbCheck As WorkbookSet rgToCheck = Intersect(rgToCheck, rgToCheck.Worksheet.UsedRange) 'Avoid excessive runtime if needlessly passed entire row or columnnnCols = rgToCheck.Columns.CountnnRows = rgToCheck.Rows.CountIf nnCols > rgToMinMax.Columns.Count Then MinMaxIf = "#ColumnMatch" Exit FunctionElseIf nnRows > rgToMinMax.Rows.Count Then MinMaxIf = "#RowMatch" Exit FunctionEnd If 'Determine whether Criteria is an array. If so, establish its dimensions.On Error Resume NextIf VarType(Criteria) >= vbArray Then nCols = UBound(Criteria) nRows = UBound(Criteria, 2) If nCols = 0 Then nCols = 1 If nRows = 0 Then nRows = 1 ReDim vResults(1 To nRows, 1 To nCols)Else 'ReDim vResults(1 To 1, 1 To 1) n = 1 nRows = 1 nCols = 1End Ifn = nRows * nColsOn Error GoTo 0For i = 1 To nRows For j = 1 To nCols d = IIf(bMinimum, 1E+308, -1E+308) 'Starting value If n = 1 Then vCriteria = Criteria Else If nCols = 1 Then vCriteria = Criteria(i) ElseIf nRows = 1 Then vCriteria = Criteria(j) Else vCriteria = Criteria(i, j) End If End If For ii = 1 To nnRows For jj = 1 To nnCols If (Application.CountIf(rgToCheck.Cells(ii, jj), vCriteria) = 1) And (rgToMinMax.Cells(ii, jj).Value <> "") Then If bMinimum Then d = Application.Min(d, rgToMinMax.Cells(ii, jj).Value) Else d = Application.Max(d, rgToMinMax.Cells(ii, jj).Value) End If End If Next Next If n = 1 Then vResults = IIf(Abs(d) = 1E+308, "#None", d) Else vResults(i, j) = IIf(Abs(d) = 1E+308, "#None", d) End If NextNextMinMaxIf = vResultsEnd Function

Have attached spreadsheet to assist and show what I mean. Hope someone can resolve this for me.

With Excel 2010, you can array-enter formulas that refer to entire columns.

Your problem was that you didn't array-enter the formulas in that workbook. To do so:
1. Select cell C2
2. Click in the formula bar
3. Hold the Control and Shift keys down
4. Hit Enter, then release all three keys. Excel should respond by adding curly braces { } surrounding your formula. It will also return the desired answer. If not, repeat.

Once the formula in cell C2 works correctly, doubleclick the little square at bottom right corner of the selection marquee. This will copy that formula down until the end of data. For your sample workbook, it will take about 30 seconds for it to finish recalculating and update the display.

Brad

0

JagwarmanAuthor Commented:

Thanks for that, sorry I was such a dumb ass.

0

Featured Post

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

q-28138022.xlsx