Thread: Summarising Worksheet Data

We have a spreadsheet that has worksheets names 06180 to 06189. I want to
create a summary worksheet of some of the data. The worksheets are identical
and the data that I want will always be the same.

The worksheets are named 06180 to 06189.

Date (H7)
Supplier (B8)
Purchase Order Number (H9)
$ Amount (P40)

I have not used macros or VBA before and I am thinking this is the only way
to be able to summarise the data that I want.

Also at the end of every month we use a new spreadsheet so how do make it so
its easy for me to change formulas etc for a new spreadsheet??

It is unclear what you mean by summarizing text values or dates. You can
sum "amount"/a number.

=sum('06180:06189'!P40)

You can put in two dummy sheets, for example one name Start and one named
End, then put all your sheets and any new sheets between these two sheets
(in the tab order). Then your formula

=sum(Start:End!P40)

will include new sheets you add.

Look in Excel help at 3D references.

--
Regards,
Tom Ogilvy



"Jade" <Jade@discussions.microsoft.com> wrote in message
news:B82022BE-D1AF-4AB1-9D3A-0CA2E55827C4@microsoft.com...
>
> We have a spreadsheet that has worksheets names 06180 to 06189. I want to
> create a summary worksheet of some of the data. The worksheets are
identical
> and the data that I want will always be the same.
>
> The worksheets are named 06180 to 06189.
>
> Date (H7)
> Supplier (B8)
> Purchase Order Number (H9)
> $ Amount (P40)
>
> I have not used macros or VBA before and I am thinking this is the only
way
> to be able to summarise the data that I want.
>
> Also at the end of every month we use a new spreadsheet so how do make it
so
> its easy for me to change formulas etc for a new spreadsheet??
>

Hi Tom

I want a worksheet that has columns named date, supplier, purchase order
number & $Amount and a formula or something that will put the data in the
above mentioned columns. Cell references are:

Date (H7)
Supplier (B8)
Purchase Order Number (H9)
$ Amount (P40)


I have used this formula ='06180'!H7 but when I copy it doesn't change the
worksheet number but changes the cell reference which is not what I want. So
I tried =SUM('06180:06189'!H7) but it doesn't give me any data just a 0.

I hope I have explained myself better this time.

Thanks

"Tom Ogilvy" wrote:

> It is unclear what you mean by summarizing text values or dates. You can
> sum "amount"/a number.
>
> =sum('06180:06189'!P40)
>
> You can put in two dummy sheets, for example one name Start and one named
> End, then put all your sheets and any new sheets between these two sheets
> (in the tab order). Then your formula
>
> =sum(Start:End!P40)
>
> will include new sheets you add.
>
> Look in Excel help at 3D references.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Jade" <Jade@discussions.microsoft.com> wrote in message
> news:B82022BE-D1AF-4AB1-9D3A-0CA2E55827C4@microsoft.com...
> >
> > We have a spreadsheet that has worksheets names 06180 to 06189. I want to
> > create a summary worksheet of some of the data. The worksheets are
> identical
> > and the data that I want will always be the same.
> >
> > The worksheets are named 06180 to 06189.
> >
> > Date (H7)
> > Supplier (B8)
> > Purchase Order Number (H9)
> > $ Amount (P40)
> >
> > I have not used macros or VBA before and I am thinking this is the only
> way
> > to be able to summarise the data that I want.
> >
> > Also at the end of every month we use a new spreadsheet so how do make it
> so
> > its easy for me to change formulas etc for a new spreadsheet??
> >
>
>
>

=indirect(Text(6179+row(A1),"00000")&"!H7)

Drag fill down

--
Regards,
Tom Ogilvy

"Jade" <Jade@discussions.microsoft.com> wrote in message
news:75D91D28-7603-45F1-AE81-DA982EB2FC1D@microsoft.com...
> Hi Tom
>
> I want a worksheet that has columns named date, supplier, purchase order
> number & $Amount and a formula or something that will put the data in the
> above mentioned columns. Cell references are:
>
> Date (H7)
> Supplier (B8)
> Purchase Order Number (H9)
> $ Amount (P40)
>
>
> I have used this formula ='06180'!H7 but when I copy it doesn't change the
> worksheet number but changes the cell reference which is not what I want.
So
> I tried =SUM('06180:06189'!H7) but it doesn't give me any data just a 0.
>
> I hope I have explained myself better this time.
>
> Thanks
>
> "Tom Ogilvy" wrote:
>
> > It is unclear what you mean by summarizing text values or dates. You
can
> > sum "amount"/a number.
> >
> > =sum('06180:06189'!P40)
> >
> > You can put in two dummy sheets, for example one name Start and one
named
> > End, then put all your sheets and any new sheets between these two
sheets
> > (in the tab order). Then your formula
> >
> > =sum(Start:End!P40)
> >
> > will include new sheets you add.
> >
> > Look in Excel help at 3D references.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Jade" <Jade@discussions.microsoft.com> wrote in message
> > news:B82022BE-D1AF-4AB1-9D3A-0CA2E55827C4@microsoft.com...
> > >
> > > We have a spreadsheet that has worksheets names 06180 to 06189. I want
to
> > > create a summary worksheet of some of the data. The worksheets are
> > identical
> > > and the data that I want will always be the same.
> > >
> > > The worksheets are named 06180 to 06189.
> > >
> > > Date (H7)
> > > Supplier (B8)
> > > Purchase Order Number (H9)
> > > $ Amount (P40)
> > >
> > > I have not used macros or VBA before and I am thinking this is the
only
> > way
> > > to be able to summarise the data that I want.
> > >
> > > Also at the end of every month we use a new spreadsheet so how do make
it
> > so
> > > its easy for me to change formulas etc for a new spreadsheet??
> > >
> >
> >
> >