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

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
>
>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
> >>
> >>
> >>
> >>
>
>
>

thanks but rounddown is not available in vba
"David" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >>
>> >>
>> >>
>> >>
>> >>
>>
>>
>>