Summarising Worksheet Data

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