Using a lookup to determine which cells to sum 1

[grantwilliams]

Hi,

Does anyone know (programmatically or otherwise) how to use a lookup to determine which cells should sum?

The circumstances are:

These are financial reports where the user enters which financial period the reports are being run for.

The forcasting for the remainder of the year needs to look up which cells correspond to the remaining periods of the financial year and sum these cells.

I've used something very similar previously to autofill the next month. Here is the code I used.

Sub Report_Setup()
'
' Report_Setup Macro
'
    Dim SelectCells, FillCells
        
    Sheets("Data").Select
    SelectCells = Range("D23")
    FillCells = Range("D23") & ":" & Range("E23")
    
    Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", _
        "Sheet7", "Sheet8")).Select
    ActiveWindow.SmallScroll Down:=-33
    Columns(SelectCells).Select
    Selection.AutoFill Destination:=Columns(FillCells), Type:=xlFillDefault
    Columns(FillCells).Select
    Columns(SelectCells).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Activate

End Sub

Cells D23 and E23 contained lookup formulas which determine which columns needed to be autofilled based on the financial period entered by the user.

TIA,

Grant

 

[macropod]

Hi Grant,

To sum the values corresponding to a date range, you could use something like:
=SUMIF(A:A,">"&E1-1,B:B)-SUMIF(A:A,">"&E2,B:B)
where-
. column A holds the dates to be evaluated;
. column B holds the corresponding values;
. cell E1 holds the starting date; and
. cell E2 holds the ending date.

Thus, for the period for which actual data are available, the start date would presumably be 1 July. Likewise, the end date would be whatever variable date the person extracting the report wants.

Conversely, for the period for which actual data are not available, the start date would be whatever variable date the person extracting the report wants and the end date would be 30 June.

So, if your forecast data are in column C, the formula to tally both the expenditure from 1 July up to today's date, plus forecast expenditure from now to 30 June, you could use a formula like:
=SUMIF(A:A,">"&DATE(2005,7,1)-1,B:B)-SUMIF(A:A,">"&TODAY(),B:B)+SUMIF(A:A,">"&TODAY()-1,C:C)-SUMIF(A:A,">"&DATE(2006,7,1),C:C)
Obviously, you could use 'DATE(2005,6,30)' instead of 'DATE(2005,7,1)-1', but I expressed the formula that way for consistency with the previous example.

Modify as needed to suit your needs.

It would equally be possible to produce a forecast expenditure for a whole financial year from expenditure to date, or on the basis of an assumed periodic expenditure rate, but it's not clear to me whether that (or the above solution) is what you're after.

Cheers

 

[grantwilliams]

Macropod,

Thanks for your advice here. I came up with a user-defined function last night however.

Function EvaluateForecast(RowNumber, DataCell, SourceSheet)

    Dim EvaluateText
    EvaluateText = "SUM(" & SourceSheet & "!" & DataCell & _
        RowNumber & ":" & SourceSheet & "!O" & RowNumber + 1 & ")"
    EvaluateForecast = Application.Evaluate(EvaluateText)

End Function

With this function, all I need to do is pass it the Row Number of the first cell that needs to be summed, the cell which contains the lookup for the first column to sum and the sheet name that the values need to be summed from.

Where this works for me is that each month, I replace forecast data with actual data for that month (I do this programmatically using the auto-fill macro I have above). I then need to reduce the number of "remaining months" summed by one.

If you've got any thoughts or suggestions on using this function, I'd love to hear them.

Grant

 

[grantwilliams]

Better yet - and the simplest method of all......

I want the sum of the "remaining months" to be displayed. Each month, I replace my forecast data with the actual data for the month. All I need to do then is sum the forecast and actuals for the whole year then subtract the year to date actuals!

it's ALWAYS something so simple!