Need a short cut for this...

Wow I didn't notice that option.
I am a kid in your circle, but I a genious in my circle.
Thanks a million Mr Ron.
Saturday/sunday was holiday for me hence the delay to reply.

"Ron Rosenfeld" wrote:

> Eddy,
>
> It would be possible to write a UDF to perform what you want. But why
> re-invent the wheel if you don't have to? Morefunc has an option to include
> itself in the workbook. You may include only the functions, or also include
> the help file.
>
> My recommendation will be to install Morefunc; then you will see in the Excel
> Tools menu (top menu bar) a Morefunc option. One of the selections will be
> "Include Morefunc in the Workbook"
>
> This would mean that if you are distributing the Workbook, Morefunc would go
> along with it.
>
>
>
> On Thu, 16 Feb 2006 21:47:27 -0800, Eddy Stan
> <EddyStan@discussions.microsoft.com> wrote:
>
> >Hi Ron,
> >Is there any way to use existing excel 2003 functions ! bcos, if I use the
> >"threeD", I need to send the pack to all other users of this file. It is a
> >common file to be used by many in my office/managers at other cities.
> >Try me some trick in Excel 2003 itself ! please x 10 million.
> >
> >"Ron Rosenfeld" wrote:
> >
> >> On Wed, 15 Feb 2006 05:28:29 -0800, Eddy Stan
> >> <EddyStan@discussions.microsoft.com> wrote:
> >>
> >> >I have three ranges in different sheets, for a selected luk up CODE 1003 I
> >> >get total qty 1600 by
> >> >=SUMIF($C$5:$C$10,$C3,D$5:D$10)+SUMIF(GURU!$C$3:$C$8,SAM!$C3,GURU!D$3:D$8)+SUMIF(JOYCE!$C$3:$C$8,SAM!$C3,JOYCE!D$3:D$8)
> >> >
> >> >Amount 4600 by
> >> >SUMIF($C$5:$C$10,$C3,F$5:F$10)+SUMIF(GURU!$C$3:$C$8,SAM!$C3,GURU!F$3:F$8)+SUMIF(JOYCE!$C$3:$C$8,SAM!$C3,JOYCE!F$3:F$8)
> >> >
> >> >is there any better way, than repeating 3 times sumif. Here Sam, Guru &
> >> >Joyce are sheet names (may be if can name the ranges, is it possible to
> >> >locate qty column & total for specific query code, likewise for amount also.)
> >> >In real data will be of 300 to 500 rows in each sheet.
> >> >
> >> >QUERY CODE QTY AMOUNT
> >> > 1003 1600 4600
> >> >
> >> >SAM CODE QTY PRICE AMOUNT
> >> > 1001 200 2 400
> >> > 1004 300 2 600
> >> > 1003 400 2 800
> >> > 1004 500 2 1000
> >> > 1003 600 2 1200
> >> >
> >> >
> >> >GURU CODE QTY PRICE AMOUNT
> >> > 1001 200 2 400
> >> > 1002 200 2 400
> >> > 1003 200 3 600
> >> > 1002 200 2 400
> >> > 1003 200 5 1000
> >> >
> >> >JOYCE CODE QTY PRICE AMOUNT
> >> > 1005 200 2 400
> >> > 1004 200 2 400
> >> > 1003 200 5 1000
> >> > 1004 200 2 400
> >> > 1005 200 2 400
> >> >
> >> >thanks in advance.
> >>
> >>
> >> Unfortunately, SUMIF is not a function that is usable with a 3D reference.
> >> However, Longre's free morefunc.xll add-in is available at
> >> http://xcell05.free.fr
> >>
> >> You can then use the THREED function to create an array from a 3D reference,
> >> and then use this formula:
> >>
> >> Assumes that your data table is in A1:D20 on each sheet.
> >> Joyce and Guru are the first and last sheets in the range.
> >> CODE being looked up is in A2 on the Active Sheet.
> >>
> >> Quantity
> >> =SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THREED(Joyce:Guru!B2:B20))
> >>
> >> Amount
> >> =SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THREED(Joyce:Guru!D2:D20))
> >>
> >>
> >> --ron
> >>
>
> --ron
>

On Sun, 19 Feb 2006 20:52:26 -0800, Eddy Stan
<EddyStan@discussions.microsoft.com> wrote:

>Wow I didn't notice that option.
>I am a kid in your circle, but I a genious in my circle.
>Thanks a million Mr Ron.
>Saturday/sunday was holiday for me hence the delay to reply.
>

Well, I'm happy we developed a satisfactory solution for your problem. You may
find that morefunc has a number of useful other functions, too.

I hope you enjoyed your holiday.
--ron