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
|