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