PDA

View Full Version : [SOLVED] Sum of cells in a row on other sheet, column reference based on selected number range



Jasen
04-17-2016, 03:46 AM
Hi, I hope I can explain this and make sense - I'd love a pointer here.

I'm trying to add a range of figures from other sheets where the sheet name and ranges can vary.

The sheet name is going to be selected from a data validation list which is in cell C6. The desired sum date range (from and to) will be also via validation of a choice of 1 to 31 which will be in cells D6 and E6 respectively.

The 1st date in whichever source sheet is chosen from the validation list, will be in column F and the 31st date is in column AJ.

I'd like to be able to choose the from date, to date, source sheet and get a sum of those dates. For example if 7th to 13th is chosen then it would return to the destination cell the sum of Columns L to R of a specified row.
I'd like to do this for several rows.

I'm unsure how to approach this.
If possible to do via formulae rather than macros that would be great.

Any advice much appreciated, thanks!

p45cal
04-17-2016, 03:30 PM
Supply a file (a) so we don't make wrong assumptions about your file/sheets and (b) to make it a lot easier for us.

SamT
04-17-2016, 04:14 PM
Someone else will have to provide an Excel only formula, but here is a UDF (User Defined Function) that should work.

In VBA, you must Right Click on ThisWorkbook in the Project Explorer and Insert a module. The Code below goes in that module.

I assumed that all the sums of Dates will be in a single column like a list.

To use the UDF, the Formulas in the column of Sums of Dates will be "=SumOfDates($C$6,$D$6,$E$6)"

The UDF Code. NB: read the comments about RowOffset

Option Explicit

Private Const RowOffSet As Long = 0
'RowOffset is the difference between the Row number the sum is in
'and the Row number the sum is of. In Other Words: If the first Summing Formula
'is in Row 5 of the main sheet but the first row of dates in the other sheets is 2, then RowOffset needs to be -3.

Private Const DateOffset As Long = +5

Public Function SumOfDates(WrkSht As String, StartDate As Long, EndDate As Long) As Double
Dim Result As Double
Dim Rw As Long
Rw = Application.Caller.Row + RowOffSet
With Sheets(WrkSht)
SumOfDates = WorksheetFunction.Sum(Range(.Cells(Rw, StartDate + DateOffset), _
.Cells(Rw, EndDate + DateOffset)))
End With
End Function