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

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Fawn" schrieb im Newsbeitrag
...
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

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Fawn" schrieb im Newsbeitrag
...
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

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


--
Regards
Frank Kabel
Frankfurt, Germany


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

--
Regards
Frank Kabel
Frankfurt, Germany

"Fawn" schrieb im Newsbeitrag
...
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

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



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

--
Regards
Frank Kabel
Frankfurt, Germany

"Fawn" schrieb im Newsbeitrag
...
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

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)

--
Regards
Frank Kabel
Frankfurt, Germany

"Fawn" schrieb im Newsbeitrag
...
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



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

--
Regards
Frank Kabel
Frankfurt, Germany

"Fawn" schrieb im Newsbeitrag
...
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

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Fawn" schrieb im Newsbeitrag
...
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



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

--
Regards
Frank Kabel
Frankfurt, Germany

"Fawn" schrieb im Newsbeitrag
...
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

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Fawn" schrieb im Newsbeitrag
...
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



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

--
Regards
Frank Kabel
Frankfurt, Germany

"Fawn" schrieb im Newsbeitrag
...
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