subtotal based on ref no
 

Excel 2003 and XP
I have a spreadsheet that can't be changed so in a separate sheet I want to
be able to create totals of variable ranges.
Each range changes based on the reference no.

Eg

Ref Desc Value
1.00 HOL <= = = this row is the header row and
will be bold
1.01 1st 10.00
1.14 2nd 25.00

SOME TEXT ON ROws which prevent me from using a Pivot table
2.00 WORK <= = = this is also a header row and will be
bold
2.14 wed 105.20
2.14.1 thurs 99.6


The desired result is
Ref Desc SubTotal
1.00 HOL 35.00
2.00 WORK 204.80

The subtotal needs to be for each change in whole number to the left of the
decimal point

Any ideas

Thanks

subtotal based on ref no
 

Hi,
If you can use another column, you can value the first digit, but this would
also assume that the number is only 1 digit. In column D try:
=VALUE(LEFT(A2,1)), which would give you a digit to subtotal on.

--
David


"Newbie" wrote:

Excel 2003 and XP
I have a spreadsheet that can't be changed so in a separate sheet I want to
be able to create totals of variable ranges.
Each range changes based on the reference no.

Eg

Ref Desc Value
1.00 HOL <= = = this row is the header row and
will be bold
1.01 1st 10.00
1.14 2nd 25.00

SOME TEXT ON ROws which prevent me from using a Pivot table
2.00 WORK <= = = this is also a header row and will be
bold
2.14 wed 105.20
2.14.1 thurs 99.6


The desired result is
Ref Desc SubTotal
1.00 HOL 35.00
2.00 WORK 204.80

The subtotal needs to be for each change in whole number to the left of the
decimal point

Any ideas

Thanks

subtotal based on ref no
 

Thanks but the value can be anything

Is there a way to only ref the whole number part i.e always round down in
VBA?

"David" wrote in message
...
Hi,
If you can use another column, you can value the first digit, but this
would
also assume that the number is only 1 digit. In column D try:
=VALUE(LEFT(A2,1)), which would give you a digit to subtotal on.

--
David


"Newbie" wrote:

Excel 2003 and XP
I have a spreadsheet that can't be changed so in a separate sheet I want
to
be able to create totals of variable ranges.
Each range changes based on the reference no.

Eg

Ref Desc Value
1.00 HOL <= = = this row is the header row and
will be bold
1.01 1st 10.00
1.14 2nd 25.00

SOME TEXT ON ROws which prevent me from using a Pivot table
2.00 WORK <= = = this is also a header row and will
be
bold
2.14 wed 105.20
2.14.1 thurs 99.6


The desired result is
Ref Desc SubTotal
1.00 HOL 35.00
2.00 WORK 204.80

The subtotal needs to be for each change in whole number to the left of
the
decimal point

Any ideas

Thanks

subtotal based on ref no
 

Hi Again,
Try this formula: =ROUNDDOWN(A2,0)
--
David


"Newbie" wrote:

Thanks but the value can be anything

Is there a way to only ref the whole number part i.e always round down in
VBA?

"David" wrote in message
...
Hi,
If you can use another column, you can value the first digit, but this
would
also assume that the number is only 1 digit. In column D try:
=VALUE(LEFT(A2,1)), which would give you a digit to subtotal on.

--
David


"Newbie" wrote:

Excel 2003 and XP
I have a spreadsheet that can't be changed so in a separate sheet I want
to
be able to create totals of variable ranges.
Each range changes based on the reference no.

Eg

Ref Desc Value
1.00 HOL <= = = this row is the header row and
will be bold
1.01 1st 10.00
1.14 2nd 25.00

SOME TEXT ON ROws which prevent me from using a Pivot table
2.00 WORK <= = = this is also a header row and will
be
bold
2.14 wed 105.20
2.14.1 thurs 99.6


The desired result is
Ref Desc SubTotal
1.00 HOL 35.00
2.00 WORK 204.80

The subtotal needs to be for each change in whole number to the left of
the
decimal point

Any ideas

Thanks

subtotal based on ref no
 

thanks but rounddown is not available in vba
"David" wrote in message
...
Hi Again,
Try this formula: =ROUNDDOWN(A2,0)
--
David


"Newbie" wrote:

Thanks but the value can be anything

Is there a way to only ref the whole number part i.e always round down in
VBA?

"David" wrote in message
...
Hi,
If you can use another column, you can value the first digit, but this
would
also assume that the number is only 1 digit. In column D try:
=VALUE(LEFT(A2,1)), which would give you a digit to subtotal on.

--
David


"Newbie" wrote:

Excel 2003 and XP
I have a spreadsheet that can't be changed so in a separate sheet I
want
to
be able to create totals of variable ranges.
Each range changes based on the reference no.

Eg

Ref Desc Value
1.00 HOL <= = = this row is the header row
and
will be bold
1.01 1st 10.00
1.14 2nd 25.00

SOME TEXT ON ROws which prevent me from using a Pivot table
2.00 WORK <= = = this is also a header row and
will
be
bold
2.14 wed 105.20
2.14.1 thurs 99.6


The desired result is
Ref Desc SubTotal
1.00 HOL 35.00
2.00 WORK 204.80

The subtotal needs to be for each change in whole number to the left
of
the
decimal point

Any ideas

Thanks