sum difference cells on multiple sheets

[JulieD]

Hi Fawn

have a look at Data / Consolidate this will allow you to sum values for the
salespeople without them having to be in the same order.

Cheers
JulieD

 

[Frank Kabel]

Hi
lets assume the name of the sales person is in column A and in column B
is the value. Try the following:
- on your summary sheet put the names of all sheets in a specific
range. e.g. in cells X1:X12
- use the following formula:
=SUMPRODUCT(SUMIF(INDIRECT("'" & X1:X12 & "'!A1:A100"),"Sales person
name",INDIRECT("'" & X1:X12 & "'!A1:A100")))

 

[Fawn]

I have 12 worksheets with total month revenue on each one, however the
salespeople is not always in the same order. I was using the formula
sum(start:endb1) etc but this does not always work because of the order they
are ranked for each month.

Is there an easy and simple way to recap all 12 sheets without defining a
name range for each sheet.

Any suggestions would help

Thanks

Fawn

 

[Fawn]

Frank I am not very good at using the indirect formula

So when you have "sales person" is that the name of the person I am trying
to compare it with and for the range x1:x12 is that the name of the sheets I
have int he workbook.

For example is my sheets are name January, Feburary and March is that what
I will have in that range. Sorry for so many questions

Thanks

Fawn

 

[Frank Kabel]

Hi
yes for sales person enter the name of the desired sales guy. In X1:X12
place the name of your sheets. If you have less than 12 sheets adapt
the range qaccordingly

Frank

 

[Fawn]

Frank I have tried this and it provides me with #REF!. I think I am doing
something wrong. Here is an example of each one of my sheets


Tab will be defined as Oct, Nov, Dec, Jan and so on

Each sheet contains sales person's name and revenue for that month. What
I need is to recap all the revenue for each month without re-doing it all

For example

Column A will be sales persons name
Column B will be sales that month.

For each sheet in the workbook the salesperson is ranked so they will not
always fall on the same row.

I have tried putting the name of the tabs in the area defined for the tabs
and I cannot get this to work.

Sorry can you please help again

Thanks

Fawn

 

[Frank Kabel]

Hi
#REF would indicate that you have in the referenced range a cell
content which does not reflect a valid tab name (e.g. an empty cell or
a typo in the sheet name)

 

[JulieD]

or another error i've seen lately is a space after the sheet name on the
sheet tab - which is quite hard to spot e.g. sheet name looks like
Jan
but is really
Jan<space>
Cheers
JulieD

 

[Fawn]

Thanks this works wonderful and exactly what I needed. Is there a way of
summing two columns?

Sorry for too many questions but this is all new to me

Thanks

Fawn