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" wrote in message
...

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 a

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" wrote in message
...

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" wrote in message
...
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 a

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" wrote in message
...

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,

I've got a similar issue to Jade's. I'm wanting to have a summary page, and
it sounds like the solution listed below is what I'm after.

I want to be able to drag & fill down a column a list that references the
same cell across multiple sheets. For example, Sheet1c2 has the value from
Sheet2c2, Sheet1c3 shows Sheet3c2, Sheet1c4 shows Sheet4c2...

The reference is the same, it is the sheet that needs to change. I've tried
working through the formula posted below, but do not understand how to adapt
it for what I'm trying to do. Can each portion of the formula be explained
please?


"Tom Ogilvy" wrote:

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

Drag fill down

--
Regards,
Tom Ogilvy