MoreFunc 5.06 Add On

[pwebley]

I am operating Windows 7 with Office 2010 Pro. I would like to use the MoreFunc 5.06 package so I can use the INDIRECT.EXT function. Does any one know if this add on will work for my version of excel and my OS? I have installed it but I am not seeing it anywhere.

 

[jkpieterse]

If you installed the 64 bit version of Office 2010 you're out of luck, none of the classic COM addins will work, only .NET addins and Excel based addins work in 64 bit Excel.

Even "normal" Excel addins of many sites will cause havoc if they use API calls which have not been updated to 64 bit.

 

[pwebley]

Thank you.

 

[jayelrod]

tech support upgraded me to Windows 7 64-bit and Excel 2010 64 Bit. do you know of an alternative way to use the MCONCAT function? i'm using in a dynamic range. This was the only function i was using in the MoreFunc download. Thank you in advance.

 

[wigi]

Hello

You would have to ask Laurent Longre to expose the code or at least the logic. Alternatively, tell the board the way you use the function (what is input and what should be the end result) and probably helpers can give you a UDF. You could store the UDF in your personal macro workbook.

 

[jkpieterse]

Why did tech support "upgrade" you to 64 bit?

 

[Aladin Akyurek]

tech support upgraded me to Windows 7 64-bit and Excel 2010 64 Bit. do you know of an alternative way to use the MCONCAT function? i'm using in a dynamic range. This was the only function i was using in the MoreFunc download. Thank you in advance.

ACONCAT is a work-alike...

Add the following function code in VBA (in-between the lines) to your workbook as a module. In order to add the function to your workbook, run...

ALt+F11
Insert | Module
File | Close and Return to Microsoft Excel.
_________________________________________________________

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function
________________________________________________________

 

[jayelrod]

my apology - i was upgraded to Windows 7 64-bit and Office Professional Plus 2010 - Excel 32-bit.

 

[Aladin Akyurek]

my apology - i was upgraded to Windows 7 64-bit and Office Professional Plus 2010 - Excel 32-bit.

Morefunc is suposed to run on a 32bit system.

 

[jayelrod]

below is an example of my spreadsheet. I'm trying to put all the sales team members from column K in 1 cell in column N. I'm sure you understand the # of team members vary per oppty id in E each day i refresh.

Excel Workbook
	E	F	G	H	I	J	K	L	M	N
108	Opportunity Name/ID	Column1	Opportunity Phase	Sales Team Member	Sales Team Role	Opportunity Value	Member & Role	Change in Oppty?	Count of Team members	Pursuit Team
109	0010195721		WON	Lewis, Huey	SUPPORT	722,323	Huey Lewis-S	Yes	7	#NAME?
110	0010195721		WON	Yu, susie	PRIMARY	722,323	susie Yu-P	No	7
111	0010195721		WON	Burke, sammy	PRIMARY	722,323	sammy Burke-P	No	7
112	0010195721		WON	Sauders, william	PRIMARY	722,323	william Sauders-P	No	7
113	0010195721		WON	Didion, bryan	PRIMARY	722,323	bryan Didion-P	No	7
114	0010195721		WON	Golden, Jennings	SUPPORT	722,323	Jennings Golden-S	No	7
115	0010195721		WON	Potston, Bob	SUPPORT	722,323	Bob Potston-S	No	7
116	0010199088		WON	Stobczak, Sport	PRIMARY	90,000	Sport Stobczak-P	Yes	2	#NAME?
117	0010199088		WON	Deshoohn, Jimmy	PRIMARY	90,000	Jimmy Deshoohn-P	No	2
118	0010199902		WON	Brady, Bryan	PRIMARY	1,666	Bryan Brady-P	Yes	1	#NAME?
119	0010200748		WON	Newman, Eddie	PRIMARY	900,000	Eddie Newman-P	Yes	3	#NAME?
120	0010200748		WON	Katt, Mickey	PRIMARY	900,000	Mickey Katt-P	No	3
121	0010200748		WON	Nelson, Mary	PRIMARY	900,000	Mary Nelson-P	No	3
122	0010200803		WON	Lattimer, John	PRIMARY	190,425	John Lattimer-P	Yes	5	#NAME?
123	0010200803		WON	Smith, Girdy	PRIMARY	190,425	Girdy Smith-P	No	5
124	0010200803		WON	Fadayomi, Nellie	PRIMARY	190,425	Nellie Fadayomi-P	No	5
125	0010200803		WON	Samuel, Sammy	PRIMARY	190,425	Sammy Samuel-P	No	5
126	0010200803		WON	Thomas, Tim	PRIMARY	190,425	Tim Thomas-P	No	5
127	0010200933		WON	Ushijima, Vira	SUPPORT	15,500	Vira Ushijima-S	Yes	3	#NAME?
128	0010200933		WON	Ordway, Amy	OTHER	15,500	Amy Ordway-O	No	3
129	0010200933		WON	Cochrane, Michael	OTHER	15,500	Michael Cochrane-O	No	3
130	0010202717		WON	Difazio, Minny	SUPPORT	197,155	Minny Difazio-S	Yes	1	#NAME?
Team Members Closed

Excel 2010

Cell Formulas
Range	Formula
K109	=REPLACE(H109,1,FIND(", ",H109)+1,"")&" "&LEFT(H109,FIND(",",H109)-1)&IF(I109="PRIMARY","-P",IF(I109="OTHER","-O","-S"))
L109	=IF($E109=$E108,"No","Yes")
M109	=COUNTIF([Opportunity Name/ID],$E109)
N109	=IF(L109="Yes",MCONCAT(OFFSET($K$108,MATCH(E109,[Opportunity Name/ID],0),,COUNTIF([Opportunity Name/ID],E109)),", "),"")