Select updated data from a range of columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have columns of data into which entry should be made at different time
intervals.

An example of the field format is given below:

Expenditure Expenditure Expenditure
as at 31/8 as at 30/9 as at 31/10


The cumulative expenditures as at the given dates would have to be entered
under the various coulmns and I would like assistance on which syntax/formula
I should use in a field called "Total Expenditures" to get the latest data
from the columns of data.

Thx
 
Are the values in the columns monthly expenditure or year to date?

Assuming monthly expenditure you would simply need to add the monthly values
together with something like =SUM(A2:C2).
If each is a year to date total, then you just need to find the maximum
value from the columns with somethign like =MAX(A2:C2). If the values are
entered as negative numbers replace MAX with MIN.
 
Thank you for your support

The expenditures are year to date, but there is a potential problem with
what you have suggested.

The total expenditure including funds which have been committed and which
may be liquidated or cancelled at a later date, as a result the total
expenditure for say 31 August may be higher than that of 30 September as some
funds might have been liquidated in September. The formula should pick out
the total expenditure as at 30 September and not the maximum as you have
suggested.

Thx once again
 
Try this macro. I've made some assumptions, as below
Assuming you have more than 1 line of monthly totals for different
products/departments.
Assuming monthly data starts in row 2
Assuming monthly data is in columns 1 to 12 (A to L)
Assuming Current total is in column 13 (M)

Private Sub Total_Expenditures()
For r = 2 To 20 ' First row of data to last row of data
If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
Cells(r, 13).Value = "" ' Make column M blank
GoTo subend ' Go to end of macro
End If
For c = 1 To 12 ' For columns A to L (12 months)
If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
continue with calculation
Next c ' Otherwise try the next column
continue:
c = c - 1 ' Move back one column from the first blank
Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column with
value
subend:
Next r ' Start on the next row of data
End Sub
 
Please can I be guided as to how I should create the macro i.e. what should I
do with the syntax below.
 
Go to Tools>Macro>Macros... and enter Total_Expenditures (or another name,
without spaces) as the name, then click Create. Copy and paste all but the
first and last lines of my code (they are already there). Close the Visual
Basic window.

I notice in the message below that some of the lines have wrapped. I have
marked the beginning of each line of code with a *. Anything in between
belongs on the line before.

To run the macro, go to Tools>Macro>Macros..., highlight the macro name and
click run.

--
Ian
--
Alylia said:
Please can I be guided as to how I should create the macro i.e. what
should I
do with the syntax below.
Click to expand...
*>> Private Sub Total_Expenditures()
*>> For r = 2 To 20 ' First row of data to last row of data
*>> If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
*>> Cells(r, 13).Value = "" ' Make column M blank
*>> GoTo subend ' Go to end of macro
*>> End If
*>> For c = 1 To 12 ' For columns A to L (12 months)
*>> If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,*>> Next c ' Otherwise try the next column
*>> continue:
*>> c = c - 1 ' Move back one column from the first blank
*>> Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column
with*>> subend:
*>> Next r ' Start on the next row of data
*>> End Sub
 
Back
Top