Select updated data from a range of columns
 

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.

--
Ian
--
"Alylia" wrote in message
...
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

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

"Ian" wrote:

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.

--
Ian
--
"Alylia" wrote in message
...
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

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

--
Ian
--
"Alylia" wrote in message
...
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

"Ian" wrote:

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.

--
Ian
--
"Alylia" wrote in message
...
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

Please can I be guided as to how I should create the macro i.e. what should I
do with the syntax below.

"Ian" wrote:

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

--
Ian
--
"Alylia" wrote in message
...
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

"Ian" wrote:

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.

--
Ian
--
"Alylia" wrote in message
...
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

Go to ToolsMacroMacros... 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 ToolsMacroMacros..., highlight the macro name and
click run.

--
Ian
--
"Alylia" wrote in message
...
Please can I be guided as to how I should create the macro i.e. what
should I
do with the syntax below.

"Ian" wrote:

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

--
Ian
--
"Alylia" wrote in message
...
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

"Ian" wrote:

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.

--
Ian
--
"Alylia" wrote in message
...
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