Setting up variable summary sheet.

[shakey1181]

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?

[robert111]

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

[shakey1181]

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

[robert111]

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

[shakey1181]

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