Hi there,

I have a workbook with 5 sheets which I would like to show all the
information from on a front sheet. The problem I have is that, although the
fields are the same, the length of the back sheets is constantly changing, so
I can't use a simple ref system. Is there anyway to set this up? I have tried
recording macros with no results as I had to specify copied ranges.

Any thoughts?

Let us assume that each sheet has only 2 columns, col A = product and
col B = sales.

in the summary sheet, try this

=sumproduct((sheet2!$a$2:$a$10000="toys")*(sheet2! $b$2:$b$10000))+sumproduct((sheet3!$a$2:$a$10000=" toys")*(sheet3!$b$2:$b$10000))+sumproduct((sheet4! $a$2:$a$10000="toys")*(sheet4!$b$2:$b$10000))

this will give you total sales for toys from all sheets, on subsequent
rows in your summary sheet substitute "tools", "radios" etc etc


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=572980

Apologies, I didn't make it clear, and I'm really bad at descriptions.

Example:

Sheet 2:
date status Project result
01/06/2006 pending dan completed
02/06/2006 pending dan completed

Sheet 3:
date status Project result
01/06/2006 pending james waitlist
02/06/2006 pending james waitlist

so sheet 1 should look like:
date status Project result
01/06/2006 pending dan completed
01/06/2006 pending james waitlist
02/06/2006 pending dan completed
02/06/2006 pending james waitlist


and if any changes are made to sheets 2 or 3, then these would update on
sheet 1. I think i have confused the issue by saying summary, perhaps
'overview' would be a better way to say it.

Hope this makes more sense and thanks for your response.

"robert111" wrote:


Let us assume that each sheet has only 2 columns, col A = product and
col B = sales.

in the summary sheet, try this

=sumproduct((sheet2!$a$2:$a$10000="toys")*(sheet2! $b$2:$b$10000))+sumproduct((sheet3!$a$2:$a$10000=" toys")*(sheet3!$b$2:$b$10000))+sumproduct((sheet4! $a$2:$a$10000="toys")*(sheet4!$b$2:$b$10000))

this will give you total sales for toys from all sheets, on subsequent
rows in your summary sheet substitute "tools", "radios" etc etc


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=572980

Can you merely list the contents of each sheet on the summary sheet,
eg

.................sheet 2.........................................sheet
3
.........date.....detail1......detail
2.............date.....detail1......detail 2


using =if(sheet2!cellref="","",sheet2!cellref)

that way you won't get loads of zeros showing


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=572980

I can't get this to work either. One thought was a cut and paste macro.

i.e copy sheet2 & 3 into sheet1, but for it to first find the last entry and
only paste from there. as long as the sheets were copied in order, this
should work.

Slight drawback: I don't know how to find the last cell with an entry, or
how to put that into a macro

"robert111" wrote:


Can you merely list the contents of each sheet on the summary sheet,
eg

.................sheet 2.........................................sheet
3
.........date.....detail1......detail
2.............date.....detail1......detail 2


using =if(sheet2!cellref="","",sheet2!cellref)

that way you won't get loads of zeros showing


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=572980