Hello, don't want to be pest on my Duplicate Questions, but I was hoping
someone could give me a shorter formula for the one I have here.

I have 31 sheets representing 31 days (DAY1 - DAY31), of the calender
in which I check for dupes in the Phone Number Column E6-E35 in all
sheets, This is the Formula that works, but I want it shorter if
Possible.. Thanks for any Help:

=SUM(COUNTIF(INDIRECT("DAY"&{1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27 ,28,29,30,31}&"!E6:E35"),"="&E6))


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=560605

=SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))& "!E6:E35"),"="&E6))

it becomes an array formula now

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mhz" wrote in message
...

Hello, don't want to be pest on my Duplicate Questions, but I was hoping
someone could give me a shorter formula for the one I have here.

I have 31 sheets representing 31 days (DAY1 - DAY31), of the calender
in which I check for dupes in the Phone Number Column E6-E35 in all
sheets, This is the Formula that works, but I want it shorter if
Possible.. Thanks for any Help:


=SUM(COUNTIF(INDIRECT("DAY"&{1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15,16,17,18,19
,20,21,22,23,24,25,26,27,28,29,30,31}&"!E6:E35")," ="&E6))


--
Mhz
------------------------------------------------------------------------
Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=560605

OT: Bob, Congrats !
Your pick of Italy to lift the World Cup was unerringly spot-on ..
[ ISWINNER("Italy") returned TRUE <g ]
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thank-you Max, just a pity it ended as it did.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Max" wrote in message
...
OT: Bob, Congrats !
Your pick of Italy to lift the World Cup was unerringly spot-on ..
[ ISWINNER("Italy") returned TRUE <g ]
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Bob Phillips" wrote:
Thank-you Max, just a pity it ended as it did.
Yes, ISERROR("Zizou's last act") = TRUE, unfortunately ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks Bob for the formula. I'm not getting the same results as the
formula I presented, but I'll keep working at it... Thanks for your
time..


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=560605

It does work. I have posted a demo at http://cjoint.com/?hnjT5ekSjd

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mhz" wrote in message
...

Thanks Bob for the formula. I'm not getting the same results as the
formula I presented, but I'll keep working at it... Thanks for your
time..


--
Mhz
------------------------------------------------------------------------
Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=560605

Hello Bob, Yes, it gets no simpler than the model you provided and
suprisingly, to me, it does work!

For some reason I notice the brackets " { } " outlining your formula,
when I copy and paste it, something strange happens, the actual results
change from 8 to 2 without those brackets around the formula.. Not sure
why, but I believe that has something to do with different outcome I'm
getting.

By the way, the "8 to 2" I mentioned is the count result in your test
program. The actual result under the shortened formula without those {
} brackets results in 2 instead of 8 like the long formula. Thanks In
Advance


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=560605

In my original response I did add ... it becomes an array formula now ...

I did not expand on that, (wrongly it seems) assuming you would understand
(especially as you had come up with such a sophisticated original formula
<g).

What this means is that the formula is an array formula. An array formula
should be committed with Ctrl-Shift-Enter (CSE), not just Enter. When you
commit it in this fashion, Excel inserts the surrounding {...} (braces).
Every time that you edit the formula, the braces disappear whilst in edit
mode, but you must commit with CSE again to re-set it as an array formula.

The 2 probably comes about because that is the matching values in the first
sheet.

Apologies for not explaining that first time.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mhz" wrote in message
...

Hello Bob, Yes, it gets no simpler than the model you provided and
suprisingly, to me, it does work!

For some reason I notice the brackets " { } " outlining your formula,
when I copy and paste it, something strange happens, the actual results
change from 8 to 2 without those brackets around the formula.. Not sure
why, but I believe that has something to do with different outcome I'm
getting.

By the way, the "8 to 2" I mentioned is the count result in your test
program. The actual result under the shortened formula without those {
} brackets results in 2 instead of 8 like the long formula. Thanks In
Advance


--
Mhz
------------------------------------------------------------------------
Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=560605

Oh Man, what a Genious you are! ... You should definately be Paid well
for your expertise Bob... That was the Problem..:) Thanks Very Very
Much!!

I now have a usable formula that is not a Page Long ;) Thanks very
much, your help is invaluable...


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=560605