creating template

I am creating a template to calulate information that is downloaded from
another program. The information is downloaded into different sheets and i
need to do lookups to bring the information into one sheet. The problem i
have is that there are subtotals to be placed on the sheet but the
information is not always in the same format. In one download there may be
four items to calculate while in another download there may be only three.
Is there any programming I can use to calculate the subtotals once all the
items are in place?

Thanks

enyaw,
Does the Data>Subtotals achieve your goal ?

NickHK

"enyaw" > wrote in message
...
> I am creating a template to calulate information that is downloaded from
> another program. The information is downloaded into different sheets and
i
> need to do lookups to bring the information into one sheet. The problem i
> have is that there are subtotals to be placed on the sheet but the
> information is not always in the same format. In one download there may
be
> four items to calculate while in another download there may be only three.
> Is there any programming I can use to calculate the subtotals once all the
> items are in place?
>
> Thanks

The info is dowloaded into other sheets. The main sheet uses lookups to
gather all the info together to calculate it.
e.g.
Part No Seq No Description Labour Material Total
1111 2 printer 10 15
25
1112 2 welder 15 10
25
Subtotal 25 25
50

The only thing i type in is the Part no and the rest is picked up by lookups
When i have all the items in that sequence inputted i need to be able to do
a subtotal on them automatically.

"NickHK" wrote:

> enyaw,
> Does the Data>Subtotals achieve your goal ?
>
> NickHK
>
> "enyaw" > wrote in message
> ...
> > I am creating a template to calulate information that is downloaded from
> > another program. The information is downloaded into different sheets and
> i
> > need to do lookups to bring the information into one sheet. The problem i
> > have is that there are subtotals to be placed on the sheet but the
> > information is not always in the same format. In one download there may
> be
> > four items to calculate while in another download there may be only three.
> > Is there any programming I can use to calculate the subtotals once all the
> > items are in place?
> >
> > Thanks
>
>
>

If you only will have one part number at any one time, just put in sum
formulas that address the largest amount of data that you will retrieve.

You can hide intervening blank rows if you need to print a report.

--
Regards,
Tom Ogilvy


"enyaw" wrote:

> The info is dowloaded into other sheets. The main sheet uses lookups to
> gather all the info together to calculate it.
> e.g.
> Part No Seq No Description Labour Material Total
> 1111 2 printer 10 15
> 25
> 1112 2 welder 15 10
> 25
> Subtotal 25 25
> 50
>
> The only thing i type in is the Part no and the rest is picked up by lookups
> When i have all the items in that sequence inputted i need to be able to do
> a subtotal on them automatically.
>
> "NickHK" wrote:
>
> > enyaw,
> > Does the Data>Subtotals achieve your goal ?
> >
> > NickHK
> >
> > "enyaw" > wrote in message
> > ...
> > > I am creating a template to calulate information that is downloaded from
> > > another program. The information is downloaded into different sheets and
> > i
> > > need to do lookups to bring the information into one sheet. The problem i
> > > have is that there are subtotals to be placed on the sheet but the
> > > information is not always in the same format. In one download there may
> > be
> > > four items to calculate while in another download there may be only three.
> > > Is there any programming I can use to calculate the subtotals once all the
> > > items are in place?
> > >
> > > Thanks
> >
> >
> >

I need to input the subtotals automatically. Is there a way of inputting the
subtotal row via programming and add it to a command button. I need to know
if there is a way to input the subtotal from the cell that the cursor is in.

"NickHK" wrote:

> enyaw,
> Does the Data>Subtotals achieve your goal ?
>
> NickHK
>
> "enyaw" > wrote in message
> ...
> > I am creating a template to calulate information that is downloaded from
> > another program. The information is downloaded into different sheets and
> i
> > need to do lookups to bring the information into one sheet. The problem i
> > have is that there are subtotals to be placed on the sheet but the
> > information is not always in the same format. In one download there may
> be
> > four items to calculate while in another download there may be only three.
> > Is there any programming I can use to calculate the subtotals once all the
> > items are in place?
> >
> > Thanks
>
>
>

Private sub commandbutton1_Click()
activecell.FormulaR1C1 = "=Subtotal(9,R2C:R[-1]C)"
end Sub

the R2C says to start the sum in Row 2 of the same column as the formula.
Adjust the 2 to reflect the start of your data.

--
Regards,
Tom Ogilvy


"enyaw" wrote:

> I need to input the subtotals automatically. Is there a way of inputting the
> subtotal row via programming and add it to a command button. I need to know
> if there is a way to input the subtotal from the cell that the cursor is in.
>
> "NickHK" wrote:
>
> > enyaw,
> > Does the Data>Subtotals achieve your goal ?
> >
> > NickHK
> >
> > "enyaw" > wrote in message
> > ...
> > > I am creating a template to calulate information that is downloaded from
> > > another program. The information is downloaded into different sheets and
> > i
> > > need to do lookups to bring the information into one sheet. The problem i
> > > have is that there are subtotals to be placed on the sheet but the
> > > information is not always in the same format. In one download there may
> > be
> > > four items to calculate while in another download there may be only three.
> > > Is there any programming I can use to calculate the subtotals once all the
> > > items are in place?
> > >
> > > Thanks
> >
> >
> >

Is there a way of getting this formula to use the active cell as a reference
to the row that needs the subtotal formula instead of having to type it in in
the program?

"Tom Ogilvy" wrote:

> Private sub commandbutton1_Click()
> activecell.FormulaR1C1 = "=Subtotal(9,R2C:R[-1]C)"
> end Sub
>
> the R2C says to start the sum in Row 2 of the same column as the formula.
> Adjust the 2 to reflect the start of your data.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "enyaw" wrote:
>
> > I need to input the subtotals automatically. Is there a way of inputting the
> > subtotal row via programming and add it to a command button. I need to know
> > if there is a way to input the subtotal from the cell that the cursor is in.
> >
> > "NickHK" wrote:
> >
> > > enyaw,
> > > Does the Data>Subtotals achieve your goal ?
> > >
> > > NickHK
> > >
> > > "enyaw" > wrote in message
> > > ...
> > > > I am creating a template to calulate information that is downloaded from
> > > > another program. The information is downloaded into different sheets and
> > > i
> > > > need to do lookups to bring the information into one sheet. The problem i
> > > > have is that there are subtotals to be placed on the sheet but the
> > > > information is not always in the same format. In one download there may
> > > be
> > > > four items to calculate while in another download there may be only three.
> > > > Is there any programming I can use to calculate the subtotals once all the
> > > > items are in place?
> > > >
> > > > Thanks
> > >
> > >
> > >

It puts the formula in the activecell - as you asked.

--
Regards,
Tom Ogilvy

"enyaw" > wrote in message
...
> Is there a way of getting this formula to use the active cell as a
reference
> to the row that needs the subtotal formula instead of having to type it in
in
> the program?
>
> "Tom Ogilvy" wrote:
>
> > Private sub commandbutton1_Click()
> > activecell.FormulaR1C1 = "=Subtotal(9,R2C:R[-1]C)"
> > end Sub
> >
> > the R2C says to start the sum in Row 2 of the same column as the
formula.
> > Adjust the 2 to reflect the start of your data.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "enyaw" wrote:
> >
> > > I need to input the subtotals automatically. Is there a way of
inputting the
> > > subtotal row via programming and add it to a command button. I need
to know
> > > if there is a way to input the subtotal from the cell that the cursor
is in.
> > >
> > > "NickHK" wrote:
> > >
> > > > enyaw,
> > > > Does the Data>Subtotals achieve your goal ?
> > > >
> > > > NickHK
> > > >
> > > > "enyaw" > wrote in message
> > > > ...
> > > > > I am creating a template to calulate information that is
downloaded from
> > > > > another program. The information is downloaded into different
sheets and
> > > > i
> > > > > need to do lookups to bring the information into one sheet. The
problem i
> > > > > have is that there are subtotals to be placed on the sheet but the
> > > > > information is not always in the same format. In one download
there may
> > > > be
> > > > > four items to calculate while in another download there may be
only three.
> > > > > Is there any programming I can use to calculate the subtotals once
all the
> > > > > items are in place?
> > > > >
> > > > > Thanks
> > > >
> > > >
> > > >