Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)Flag for United States of America

asked on 

Excel Zone Expert Discussion, Number 28

This thread is for general discussion about all things related to the Excel zone and its Experts. The Excel Regulars participate here as a matter of course, but newcomers are always welcome. Topics might include:

- Asking for assistance with specific questions
- Issues, announcements, and discoveries related to this zone
- Discussing Experts Exchange features and functions
- Recognition of new Experts, amazing posts, and accomplishments
- Getting to know your fellow Experts
- Planning meets and greets with other Experts when traveling

And, occasionally, a little humor and other completely unrelated topics.

Drivel posts such as "First Post" or "Subscribing" will be deleted. There is a link to the right with the text "Monitor". Click it. It does magical things.

This thread must not be used for any of the following:

- Presenting any specific member in a negative light
- Suggesting that any specific member needs to change their behavior
- Suggesting that others blacklist any Asker for any reason

These topics and any others like them should be taken up privately with the site moderators, all of whom are listed here with their email addresses:

   https://www.experts-exchange.com/communityService.jsp?editVolunteerMenuIndex=1

Previous Expert Discussion: http:/Q_26923339.html

Next Expert Discussion: http:/Q_27176743.html

---

Zone Advisors

When this thread reaches a page size of about 100K (about 200 posts):

1) Create a new "Expert Discussion" thread using this content as a template, incrementing the number in the title, and setting the "Previous Expert Discussion" link to this thread.

2) Edit the new thread and set the points to zero.

3) Edit this question and set the "Next Expert Discussion" to link to the new thread.

4) Make a final post in this thread with a link to the new thread "Next Expert Discussion: http:Q_xxxxxxxxx.html" and accept that post as the solution.
Microsoft Excel

Avatar of undefined
Last Comment
Dennis Wallentin
Avatar of TommySzalapski
TommySzalapski
Flag of United States of America image

Anyone with Excel 2010 want to see if they can replicate the error here?
Anyone who's really good at messing things up can try too. I can't see why the asker would see the error.
http:Q_27038992.html
Avatar of Arno Koster
Arno Koster
Flag of Netherlands image

According to the Microsoft knowledge base this could be caused by named references inside the sheet to be copied.
In Excel 2007 duplicate names can exist in a single workbook, but maybe excel 2010 cannot handle this.

The MS KB article indicates that the error should only occur after 200 or so worksheet copy actions, proposed workaround is saving the workbook after 100 copy actions.
Avatar of rspahitz
rspahitz
Flag of United States of America image

wow!  that just seems like an arbitrary limit, and that sounds like some kind of memory leak that loses something every time you copy.
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Requesting a Mod to change the points to 0 on this thread please

https://www.experts-exchange.com/questions/27041752/Congrats-to-our-New-Excel-Genius-jppinto.html

Sid

Done -- aikimark

Open in new window

Avatar of Tracy
Tracy
Flag of United States of America image

Hey guys,

Can someone take a look at this one:

https://www.experts-exchange.com/questions/27042100/Error-calling-Class-Module.html

I have a text file (.ini) that is encrypted and I need to make a change to the server name in the file.  I know what string I need to replace in the ini file, but I need to know what it should be replaced with.  So I'm trying to encrypt a new text file with the server name in it.  Once it's encrypted, then I can just copy the string to the ini file and it should be changed to the new server name.

The workbook is using class modules, and I'm a little light on those, I tend to not use them.  Any help is greatly appreciated.

I created my own company and have some new contracts so I've been super busy, which is why I've been mostly MIA this year.  Hope all is well with everyone.

Thanks,
Tracy
Avatar of Tracy
Tracy
Flag of United States of America image

I have another question related to the one above.  It's more complicated though, because it deals with encryption.  I didn't write the code, I'm just trying to modify it.

https://www.experts-exchange.com/questions/27043344/Changing-a-value-in-an-encrypted-file-using-VBA.html

Thanks,
Tracy
Good luck with that new venture, Tracy!
Avatar of Tracy
Tracy
Flag of United States of America image

Thanks!
Need another pair of eyes here:

https://www.experts-exchange.com/questions/27045822/user-defined-function-for-last-saved-date-time-not-updating.html

User has a volatile UDF that returns the last saved date/time, and we are using a BeforeSave event sub to force a recalc to get the thing to update.

Works for me, doesn't work for him, and I have no clue as to why.
Can someone please have a look at this one: http:/Q_27047042.html
I don't think that pivot tables will be a satisfactory solution for the asker, and I don't have the VBA to cover it.

thanks, teylyn
Avatar of rspahitz
rspahitz
Flag of United States of America image

teylyn, I'm not sure that I have to assist with all of that, but maybe I can assist with parts of it.  I don't want to post at this point or I'll feel obliged to follow through and may not be able to complete it.

Why does he not like the idea of filters?  Or does he but he need a VBA UserForm to allow for selection criteria?  I could assist you in giving him that.

Pivot tables are great for individual what-if reports, but not so good for ongoing reports or multiple reports (where you need many pivot tables that may end up overlapping on a page, I suppose, right?)
rspahitz, thanks for looking at this. I think that there just is not enough numerical data to warrant a pivot table. In 2010 a layout with just the text would be possible, but not in 2007. As far as I see, the asker does not want to summarize, anyway, just to filter, but with an easy-to-use, fool-proof user interface.
Avatar of rspahitz
rspahitz
Flag of United States of America image

So it sounds like a Form.  Feel free to copy/paste the following into the question.  If user likes this idea, post back here and I'll come in to assist you with completing it.

Go to VBA (Alt-F11) and menu Insert | User Form. You'll get a little tool bar with controls.
Add a button and set it to btnExit, text E&xit and code it with: Unload Me
Add a button and set it to btnOK, text &OK and code it with things to be determined.
Add a few labels and text boxes (and/or comboboxes) for the types of filters to be applied, then fix the code for the OK to handle these and push them to Excel sheet.

I guess this needs to be sorted by someone with more experience in VBA than I have, after all. Here is the link again http:/Q_27047042.html
Avatar of rspahitz
rspahitz
Flag of United States of America image

Does anyone have time to help with this?  I probably can't until Saturday.

It's VBA code to open a collection of workbooks and copy sheets into a different one.

https://www.experts-exchange.com/questions/27048590/excel-vba-Must-Save-workbook-in-order-to-do-another-copy-paste.html?cid=1572&anchorAnswerId=35823920#a35823920
Avatar of rspahitz
rspahitz
Flag of United States of America image

separate...does anyone know why this lines are not creating a reference to the specified workbook:

Dim objBook As Workbook
Set objBook = Workbooks.Open(BookName, , True)

BookName is passed in a parameter, but it's a valid path and I can use Dir to confirm its existence.
When I run the above code, objBook is set to Nothing every time, regardless of the value of BookName.

I know I've done things like this before, and the help files imply that it will work.
Although they don't seem to use the Set command, I tried their way without success:

Workbooks.Open BookName, , True
Debug.print Activeworkbook ' <= this shows the original workbook, not the one I just opened.

Any ideas how to open a workbook through code?
Avatar of dlmille
dlmille
Flag of United States of America image

Funny, this works for me:

Sub openWorkbook()
Dim objBook As Workbook
Dim BookName As String

    BookName = ActiveWorkbook.Path & "\a.xls"
   
    Set objBook = Workbooks.Open(BookName, , True)
   
    Debug.Print objBook.Name '<-displays the A.XLS file I just opened
   
End Sub
Avatar of dlmille
dlmille
Flag of United States of America image

You might validate that BookName is a valid path/filename combination.  I miss that sometimes, myself.

Good luck!

Dave
Avatar of dlmille
dlmille
Flag of United States of America image

I know it seems obvious, but...

Try this - do a debug.print on BookName, go to the immediate window, copy that output, then use Excel to open that, pasting that exact path/filename in and see if that works...  I had something similar not work for me when the path LOOKED right, but wasn't, for some reason...  And it was this copy/paste in the file open that helped me understand the text string wasn't quite correct...

Dave
Avatar of rspahitz
rspahitz
Flag of United States of America image

Good ideas Dave, but no luck.  Oddly enough, if I run your code, it's fine; if I run mine within a function, it returns nothing, as seen below.

It gets to the line, I can confirm that BookName has the right value, and it returns nothing.  I try it in your code and runs that book's _Open macro! /confused/



Private Function GetCellValue(BookName As String, SheetName As String, CellName As String) As String
    On Error GoTo GetCellValue_Err
    
    Dim strCellValue As String
    Dim objBook As Workbook
    Dim objSheet As Worksheet
    
    If BookName = "" Then
        Set objBook = ActiveWorkbook
    Else
        ' open book
        Debug.Print BookName; " "; CurDir
        If Dir(BookName) = "" Then
            Set objBook = Nothing
        Else
            Set objBook = Workbooks.Open(BookName, , True)
            'Set objBook = Nothing
            'Workbooks.Open BookName, , True
        End If
    End If
    
    If objBook Is Nothing Then
        strCellValue = "#FILE"
    Else
        If SheetName = "" Then
            Set objSheet = objBook.ActiveSheet
        Else
            Set objSheet = objBook.Sheets(SheetName)
        End If
        'strCellValue = BookName & " / " & SheetName & " / " & CellName
        strCellValue = objSheet.Range(CellName).Value
    End If
    
GetCellValue_Exit:
    If BookName <> "" Then
        On Error Resume Next
        objBook.Close
    End If

    Set objSheet = Nothing
    Set objBook = Nothing
    GetCellValue = strCellValue
    Exit Function
    
GetCellValue_Err:
    strCellValue = "#N/A"
    Debug.Print Err.Description
    Resume GetCellValue_Exit
End Function
Sub openWorkbook()
    Dim objBook As Workbook
    Dim BookName As String

    BookName = "C:\Users\rob.spahitz\Documents\_MI\_POS Tests\D0_Processor_POS-NCPDP_ERR_CLOB_D0.xls"
    
    Set objBook = Workbooks.Open(BookName, , True)
    
    Debug.Print objBook.Name '<-displays the A.XLS file I just opened
    Set objBook = Nothing
End Sub

Open in new window

Avatar of aikimark
aikimark
Flag of United States of America image

Since the path contains a quote, try using a quote delimiter for the workbook name.

Chr(34) or double-quote literal character sequence.
Avatar of dlmille
dlmille
Flag of United States of America image

aikimark - glad you see the quote in the path.  Must be my eyes as I can't see it and I've scanned the code several times, lol...

Must need coffee!

Dave
Avatar of rspahitz
rspahitz
Flag of United States of America image

Apparently the problem is that Excel won't let me do this through a function.

Try adding this to a module and then create a formula that points to it:

=OpenWkBk(A2)

 
Function OpenWkBk(BookName As String) As String
    Dim objBook As Workbook
    
    Set objBook = Workbooks.Open(BookName, , True)
    OpenWkBk = objBook.Name
    Set objBook = Nothing
End Function

Open in new window


As a sub, or called from a sub, it works fine.  As an Excel function, it won't work!
Avatar of dlmille
dlmille
Flag of United States of America image

I'm not surprised.  It would then supposedly have to open a workbook everytime you changed the value in A2, right? probably wouldn't be good if it did work, I don't think.. Unless I'm missing something, lol.

Dave
Avatar of rspahitz
rspahitz
Flag of United States of America image

Potentially correct, Dave.  Maybe MS recognized this an cut off I/O functions for formulas.
Now I need a work-around to complete an answer to a question about using INDIRECT.EXT to handle this...
Maybe I can embed it into a class?  Any other ideas?

FYI...here's proof:  run the function from the sub and it works fine; run it from an Excel function and it won't open the file:

A1 = (valid path)
A2 =OpenWkBk(A1)

 
Sub openWorkbook()
    OpenWkBk Range("A1").Value
End Sub

Function OpenWkBk(BookName As String) As String
    Dim objBook As Workbook
    
    If Dir(BookName) = "" Then
        OpenWkBk = "#FILE"
    Else
        Set objBook = Workbooks.Open(BookName, , True)
        If objBook Is Nothing Then
            OpenWkBk = "#FILE2"
        Else
            OpenWkBk = objBook.Name
            Set objBook = Nothing
        End If
    End If
End Function

Open in new window

Udfs don't change the environment; refute at your peril
Avatar of rspahitz
rspahitz
Flag of United States of America image

lol...this UDF has  a read-only function...so no risk of changing the environment.
I'm not sure I totally agree, but on the surface it sounds good.
Hmmm...thinking further, I recall using the same argument with C++ developer who argued that it's okay to pass information back through the parameter arguments rather than handling it the correct way as a return value.  If you pass a parameter, in my opinion, it should always be an IN parameter.  If you want the value of a parameter to be changed (ByRef), then use a return value that allows the new value to be passed back, then set it.

Anyway, if this Open is not allowed, I'll see about offering a push-button solution rather than a function solution, but all user wanted was to replace the INDIRECT.EXT function, which does an external file read anyway.
Avatar of dlmille
dlmille
Flag of United States of America image

on to more trivia, but helpful stuff:

I've got a function returns variant which I plan to address from an excel formula. Everything works fine, until I try to set the function equal to an array of very large size.  What's the max array size can be passed to a UDF function addressed from an Excel sheet?

Thanks!

Dave
Which version of Excel?
Avatar of dlmille
dlmille
Flag of United States of America image

2007
Avatar of dlmille
dlmille
Flag of United States of America image

http:/Q_27058345.html - a bit of a challenge...

Dave
Can I say I think it is most unreasonable, I have been trying to beat Rory to my genius in MS Office but he has persistently maintained his lead ... s'not fair!

Watch this space as in a week or two he'll be there.

Chris
Depends how many Qs they cross post in Excel. ;)
Avatar of Arno Koster
Arno Koster
Flag of Netherlands image

rspahitz,

likely the problem is because of the space character in the path (between "_POS"  & "Tests"). In those cases adding quotes is indeed a step towards the solution
Avatar of rspahitz
rspahitz
Flag of United States of America image

AK,

Normally I'd agree with yo, but I get the same issue with this:

C:\Users\rob.spahitz\Documents\Book1c.xls

I've tried it with the quotes and no success.
If you can get it to work, then maybe it's a configuration on my side and the person asking the original question that inspired this investigation.
Avatar of Arno Koster
Arno Koster
Flag of Netherlands image

I guess we're out of luck :

if I try to create a new excel application by set app = createobject("Application.Excel"), an error message pops up : activex cannot make an object.
Executing the workbook.open statement does not even raise an error but simply does nothing. I guess the error handling is done somewhere underwater.

Indeed it seems that the problem is not the path identifier but the open statement itself, most probably as a security precaution. Fiddling with application.AutomationSecurity does not help though.
Activating open file dialogs also does not work.
Avatar of rspahitz
rspahitz
Flag of United States of America image

yeah, and it's only for functions or subs launched from functions.  I'm still trying to think of a way to have the function launch a separate thread to trigger a sub, like with a timer control (that's not in Excel but is there in Access) or maybe an external batch file to trigger a run to a specific sub that could simply open the file and put the value somewhere so the function can grab it if it's ready.

I suspect that the "underwater" code for functions was designed to keep Excel from taking too long to handle certain I/O tasks.  that's probably why the .EXT add-in was created; to override that as a separate process.
You can do it with windows timers (settimer api) but it's not usually a good idea.
Avatar of rspahitz
rspahitz
Flag of United States of America image

I find it odd that the INDIRECT function works with external workbooks that are already open, but not with those that are not (again, probably because of too many potential issues with open-failures or open-delays.
I could launch the other workbook, but because it's only to grab a value, it would then need to be closed, which would require an API to determine where it is and...it's just not worth all the trouble for something that can be accomplished with a macro (rather than a function.)
Avatar of dlmille
dlmille
Flag of United States of America image

Especially when Vlookup and other matching functions work with linked files that are closed...

There's always INDIRECT.EXT()

Dave
Avatar of dlmille
dlmille
Flag of United States of America image

Oh, and see this link: http:/Q_27040150.html#a35862682

Dave
Avatar of rspahitz
rspahitz
Flag of United States of America image

D'oh!  I mean D'ave!

INDIRECT.EXT wasn't working for the author, which is why I went in the other direction (and I don't have .EXT on my system.)  I suspect he just wasn't using it right, but since it's an add-in, I thought I'd offer a non-add-in solution, which apparently won't work.  I think he just got his quotes wrong.  I'm waiting for his response so I can have him let me know if that fixed it.
Avatar of dlmille
dlmille
Flag of United States of America image

I guess you didn't get my link, see 3 posts above?

:)
Avatar of dlmille
dlmille
Flag of United States of America image

:)  - when I typed that, I recalled that from a few days ago, then decided to have a bit of fun

lol

Dave
Avatar of dlmille
dlmille
Flag of United States of America image

Reposting...
@byundt - and others interested

More changes to the conditionalcolor() routines.  Appears it doesn't work for complex formulas without doing a bit of patching.  Take a look - appreciate feedback.  Here's my solution/patch:  http:/Q_27069354.html#a35871412

I had to get the row of the formula and change that row number to the row where the formula was being evaluated.  I thought that was being done alread with the formula conversions, but apparently not.

Dave
Avatar of rspahitz
rspahitz
Flag of United States of America image

dlmille:
@rspahitz

>>mine was from some weird Microsoft settings to an "@" e-mail.

Send me the link, if your sender email address tip is posted, or share if you can...  The question I answered was satisfied with send on behalf, so not urgent, just for learning.

Dave
-----
Here's the pieces you may be looking for:

Dim Item As MailItem
    Dim objMI As Object 'MailItem/MeetingItem
    Dim objRecipientList As Recipients
    Dim objRecipient As Recipient

            Set objMI = ActiveInspector.CurrentItem ' obj(1).CurrentItem
    If Not objMI Is Nothing Then
        Set objRecipientList = objMI.Recipients
            Set objRecipient = objRecipientList(iRecipientCntr)
            If InStr(objRecipient.Address, "@") > 0 Or InStr(1, objRecipient.Address, "/O=" & strSendingServer, vbTextCompare) <> 0 Then
...

From these pieces, I think you can figure out the rest.  If not, let me know since I left out a few pieces that seem to be irrelevant.
Avatar of dlmille
dlmille
Flag of United States of America image

@rspahits - thanks


Dave
Avatar of dlmille
dlmille
Flag of United States of America image

>> rspahitz - sorry bout the spelling :)... fingers did a "sounds like" on the z with an s, for some reason.

Dave
Avatar of rspahitz
rspahitz
Flag of United States of America image

s is as good as z, and you can call me Rob is that's easier :)
OK, you solved the conditionalcolor() routines. Now the same asker needs Worksheet_change events that monitor two dozen cells turned into calculate events, since the cells contain formulas and will never ever be entered manually.

http:/Q_27075268.html
Avatar of bromy2004
bromy2004
Flag of Australia image

Hi Experts.

Can I get a bit of help with http:/Q_27073036.html?

I've got an idea with a SUMPRODUCT but I haven't used Excel in 18 months, so my skills are a bit rusty.

=SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))>=E$1)*(ROW(INDIRECT($A2&":"&$B2))<=E$1+6)*(WEEKDAY((ROW(INDIRECT($A2&":"&$B2))))<6)*(ROW(INDIRECT($A2&":"&$B2))<>Holidays))
Nathan! (do I remember that correctly?) How've you been?

Sorry, that Q is out of my league, what with all the Gnatt instead of Gantt. But good to see you back here!

OT:
For the last 5-6 months or so I've been very busy. It will not be finalized by ending my marriage and that I move to my new home. At the same time it feels both scary and exciting. After 30 years I will now stand on my own.

I will be disconnected for the following 4 weeks when I move etc (I hope less time but it's not my decision).

My apologize for being inactive for a longer period. I hope I can jump on board again once I have been installed in my new home.

Thanks,
Dennis
Avatar of rspahitz
rspahitz
Flag of United States of America image

Best of luck Dennis.  While you're gone, we'll snag all those questions and points that you would have snagged from us :)
Avatar of zorvek (Kevin Jones)

ASKER

Dennis,

You've been through a lot the past few years. Stay focused on what is most important: your own path and your happiness. The future is full of wonders yet to be realized.

Kevin
Avatar of nutsch
nutsch
Flag of United States of America image

Amen to that.

Thomas
Avatar of bromy2004
bromy2004
Flag of Australia image

Teylyn,
you remembered correctly, and if I remembered correctly, i'm now a bit closer to your location (NZ) now that i'm in melbourne...Navy's taking me places

Regarding the question, thats no problems...I was thinking that Barry could whip together something 4 times smaller and faster than what I could.

Its good to be back
Nathan,

Melbourne? Ahh. Most beautiful city I've ever seen. <envious> If you stretch your neck and look east, you might just see my coastline. I think Melbourne is the closest civilised place to where I live.

Once you've found your orientation down under and sampled the wonderful food in the restaurants, are you keen to resume some cleanup work? I see you still have the badge. Broomee and I are the only active CVs in Excel, and when we both posted in a Q as experts, none of us gets that Q served for cleanup ...
Avatar of bromy2004
bromy2004
Flag of Australia image

Melbourne is pretty good, but can't hold a candle to Perth (back in three weeks)
I'm sure NZ is civilized and beeched as bru

I think I will get back into the Cleanup, I thought that it would be taken off me when I took a bit of a hiatus
Avatar of bromy2004
bromy2004
Flag of Australia image

Anyone got any ideas to help with http:/Q_27080301.html

SUMPRODUCT without the SUM

fixed the question link -- aikimark ZA

Open in new window

Avatar of rspahitz
rspahitz
Flag of United States of America image

Found another 2010 "bug" (or is it a feature?)

Reproduce:
* Open a new book.
* Protect sheet #1 (Review / Changes / Protect Sheet) using any settings.
* Go to sheet #2
* Try to change the theme (Page Layout / Theme)

You get an error indicating that you can't change the theme because the sheet is protected (even though the current sheet is not protected):

"You cannot use this command on a protected sheet."

I guess the real problem is that the theme applies to the whole book, so if ANY sheet is protected, you cannot use it.
I call it a bug because if you go to sheet 1, the Theme button is disabled (so you obviously can't use it) but on unprotected sheets, the button is enabled but cannot be used.

Where it "bit" me is that I tried to create a new color theme on an unprotected sheet, spent 5 minutes setting up the colors just the way I wanted, went to save and it told me I can't do that because I can't use the command on a protected sheet, and it aborted all my work without saving it. OUCH!

So why not disable this and any other "global" settings regardless of the sheet you're on.  By enabling it, it makes it appear that it is available for the sheet.
--
RobS
Avatar of dlmille
dlmille
Flag of United States of America image

Is there a simple, one line statement in VBA to pass an array of column numbers from a range?

E.g.,

   myVariant = getArrayofColumns(Range("A:Z"))

where myVariant(0) would be 1, myVariant(1) would be 2, all the way to myvariant(25) = 26

?

Dave
If you don't mind one based:

   myVariant = [column(A:Z)]

if it has to be 0-based, transpose it first.
Edit: ignore that last bit - it will still be 1-based.
Avatar of dlmille
dlmille
Flag of United States of America image

I see that works, but apparently needs to be zero based....

Thanks
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Why don't you ask a question? Somebody could get points.
Avatar of dlmille
dlmille
Flag of United States of America image

sounds like it should be at this point
Avatar of dlmille
dlmille
Flag of United States of America image

Done - http:/Q_27089462.html

corrected link (only use one slash) -- aikimark

Open in new window

Does anyone have better idea to do   this  ?

Is this a bug in Excel 2010?

Situation: SUMIF() formula with criteria range on another sheet. If the criteria range and the sum range occupy the same cells in the respective sheets. like A1:A5, Excel 2010 returns a result that looks like it is summing the criteria range instead of the sum range.

Scenario (see attached file):

Formula 1 =SUMIF(Data!A1:A5,5,Result!A1:A5)

Formula 2 =SUMIF(Data!B1:B5,5,Result!A1:A5)

So, in Formula 1, the criteria range and the sum range are in the same space, but on different sheets. In Formula 2, the criteria range and the sum range occupy different ranges on the different sheets.

For Formula 1, Excel 2003 (and reportedly, Excel 2007) deliver the correct result, i.e. a sum of the "sum range". Excel 2010, though delivers a sum of the "criteria range".

Open the attached file with Excel 2003 or 2007. Close it and open it with Excel 2010. If you don't have access to both of these versions, please see the screenshots below. They have been taken from the same file, first opened in Excel 2003, then opened in Excel 2010.

Has anybody come across this issue before? Is Excel 2010 confused when criteria range and sum range are in the same range ON DIFFERENT SHEETS?

Is this a bug?

cheers, teylyn

sumif2003.png
sumif2010.png
test-file.xls
Avatar of zorvek (Kevin Jones)

ASKER

Yes, this is a known issue that is well documented on the Internet.

Kevin
Avatar of zorvek (Kevin Jones)

ASKER

Also a bug in SUMIFS, AVERAGEIF, and AVERAGEIFS.

Kevin
What exactly does "well documented" mean? All I come up with is link farms.

Any MSFT attribution? KB article? I've Bingled "sumif criteria range on different sheet" but only came upon descriptions of  the problem from users, nothing from MS.

Do you have any concrete links?
Avatar of zorvek (Kevin Jones)

ASKER

Microsoft does not easily acknowledge issues until they are fixed and the fix released in a service update, and even then they are reluctant to acknowledge. There is ample discussion in the Microsoft groups and related sites. I'm sure that they are aware of the problem and, since it was not an issue in 2007, it will be resolved sooner versus later.

This is about as close to the company as you will get:

   http://answers.microsoft.com/en-us/office/forum/office_2010-excel/sumif-bug-in-excel-2010/d514046f-5e89-4159-a335-2da7853f5bf4

But even here the Excel team keeps silent and leaves the work to the MVPs and other Excel hot shots. I don't like the ethic but, if you consider how many people are working on Excel (well into the hundreds,) how complex it is, and how large the audience is, it makes sense that Microsoft does not want to stir any rumors. As soon as they give a hint of acknowledgement they are under the gun to fix it and facing a very large disappointed audience if they do not.

Kevin
Avatar of zorvek (Kevin Jones)

ASKER

By "well documented" I mean discussed in the forums regarding its behavior and reproducibility.

I didn't find any link farms. A link farm is used to do SEO and produces links to paying websites. All I found were original forum discussions.

Kevin
Thanks for that link, Kevin. Embedded in that discussion was another link to this social.technet.microsoft.com discussion, involving a few regular users, Excel MVPs and Microsoft staff. In there, a link to a hotfix can be found, but it seemed to raise more confusion than anything else.

Re my use of the term "link farms". I may have the terminology wrong. What I meant is the abundance of sites that seem to only have links to every available Q&A site and still somehow manage to be in the top ten search results for many searches. I don't know what the proper term for these smoke and mirror sites are. They feature high on the search engine results, are plastered with ads and only offer links to other sites.
Avatar of zorvek (Kevin Jones)

ASKER

Those are information re-distributors or squatters - sites that snatch an interesting domain name and then take advantage of other sites to generate ad revenue and traffic with the eventual goal of selling the domain.

A link farm is an old SEO technique to quickly produce - for a fee - large numbers of links referencing an up and coming website looking to improve their position in the organic listings. Google's page rank system is based on reciprocal links which connote community and thus relevance. Link farms simulate this but Google and other search engines have pretty much rendered them ineffective by blacklisting any site caught using them.

Kevin
Avatar of zorvek (Kevin Jones)

ASKER

Regarding the fix: I saw the thread but missed the link to the fix.

Title: Description of the Excel 2010 hotfix package (Graph-x-none.msp, Excel-x-none.msp): August 31, 2010
Link: http://support.microsoft.com/kb/2345345

Kevin
Harrgghhhh!

trying to install the hotfix, after entering three different captcha versions, I get

Hotfix Request
The system is currently unavailable. Please try back later, or contact support if you want immediate assistance.http://support.microsoft.com/contactus/?ws=support

Either my eyes need serious fixing or something else is off kilter. I'll try again tomorrow.
Avatar of Tracy
Tracy
Flag of United States of America image

Does anyone have code for the Solver addin or better yet code that does a similar thing but not as buggy?  I'm going to post a question, but I thought I'd put it in here first, because I don't want a bunch of links.

And has anyone used the solver through code, any tips or pitfulls that I might avoid?

Thanks,
Tracy
Avatar of aikimark
aikimark
Flag of United States of America image

@Tracy

Many years ago, I used to receive a catalog of Excel add-ins with a large section devoted to solvers.  Do you need a special kind of solver?
Avatar of Tracy
Tracy
Flag of United States of America image

Not that I know of.  They're using the Solver addin to solve a cash flow IRR calculation (using XIRR function for the percent) and they want to do it automatically.  But they said it's buggy if the "guess" is blank or not close.  I really haven't used it before.  In fact, I haven't used the XIRR function before either.  I haven't used any of the financial calculations really... guess it's time to learn.
Avatar of aikimark
aikimark
Flag of United States of America image

Does it ever show buggy results if the guess is .1?

I know it's the default, but maybe it isn't handling the missing guess properly.
Avatar of Tracy
Tracy
Flag of United States of America image

If it's blank or .1 it says "Solver could not find a feasible solution."  The "guess" has to be something close to what the solver is going to calculate to.  I'm going to post a sample of the data I have in the question.  I'll post link here in a few minutes after I create the question.
Avatar of Tracy
Tracy
Flag of United States of America image

I hope this link works, here's the question I posted:

http:Q_27100161.html
Avatar of Dave
Dave
Flag of Australia image

XIRR uses an inbuilt iterative solving process, see http://msdn.microsoft.com/en-us/library/bb224771%28v=office.12%29.aspx

But interestingly MSFT have obviously tweaked this in 2010 as I have seen the same XIRR formula in xl07 and xl10 provide different default answers. Both answers of course being valid, but disconcerting that different Excel versions report different "base" results

IRR is a poorly understood metric even though it is apparently simple. If I were to recommend any of the variants it would be MIRR where project returns are re-invested at the company's cost of capita

Dave
 
Avatar of Dave
Dave
Flag of Australia image

quick google search
"Undocumented improvements to XIRR in EXCEL 2010"
http://westclintech.com/Default.aspx?tabid=132&EntryID=39
Avatar of dlmille
dlmille
Flag of United States of America image

I'm trying to help a Premium user out.  I need to know if I can process > 1 million lines of .txt file input (a line at a time).  Does the 1 million row limitation limit opening files for line by line processing?

Thanks for the quick advise.

Dave
No it shouldn't apply unless you are trying to put it in a worksheet (or open it directly in Excel).
Avatar of dlmille
dlmille
Flag of United States of America image

That's what I thought.  I'm just opening, reading line by line, and outputting to another file (e.g., tweaking the input file to "correct" it to a revised output file.

Thanks!  I was about to do it with vbscript, but had to ask - nice to have the Excel IDE for development/testing.

Dave
Avatar of SiddharthRout
SiddharthRout
Flag of India image

I am not sure how are you reading it line by line but I have a suggestion. Read the file in binary format in one go and then loop through line by line. It will be much faster than directly reading it line by line. here is an example.

Private Sub Command1_Click()
    Dim MyData As String, strData() As String
    
    Open "C:\MyTextFile.Txt" For Binary As #1
    
    MyData = Space$(LOF(1))
    
    Get #1, , MyData
    
    Close #1
    
    strData() = Split(MyData, vbCrLf)
    
    '~~> Perform opeartion
    For I = 0 To UBound(strData())
        
    Next
End Sub

Open in new window


HTH

Sid
Avatar of dlmille
dlmille
Flag of United States of America image

This will work with > 1 million lines?

Thanks!

Dave
Avatar of dlmille
dlmille
Flag of United States of America image

Thanks - seems the only limitation is the size of the file.  For my setup, I was able to "fake" the size by using Space$(50000000) and that worked - I would guess that'd be 50 MB file.  Hopefully, that's good enough and the processing speed is very fast.

Thanks again!

Dave
Avatar of rspahitz
rspahitz
Flag of United States of America image

I've done the same, and reading the entire file allows for quicker processing.  However, it also hangs the application while it loads the file into memory, which could take several minutes (at least the way I do it, which is a bit different from the above, using the INPUT command.)

So for background processing, it's better to try to load the entire file; if the user is waiting, it may be better to read in blocks (or lines) so that you can report back the progress, even though it will be significantly slower (possibly 2-3 times as long.)
Avatar of dlmille
dlmille
Flag of United States of America image

May I ask a moderater to delete this post:  http:/Q_27110927.html#a6016219.  It was a bit lengthy, and I needed to make a correction, so the subsequent post is there.  Cleaning this up might make the solution more digestible!

Thanks!

Dave
Avatar of dlmille
dlmille
Flag of United States of America image

Sorry - type-o - its this post that needs deletion:  http:/Q_27110927.html#a36016219

Thanks!

Dave
Avatar of dlmille
dlmille
Flag of United States of America image

yea, old quick trigger finger Dave strikes again!

:)

Thanks!
Sorry gang, coming late to the party on the whole IRR / XIRR / MIRR thing.

As Dave B wrote, most of the people seeking to use one of the flavors (or is that flavours?) of IRR do not seem to truly understand what it is, how to use, or what its shortcomings are.

One of the hardest things, I think, for most people to understand about IRR is that it is entirely possible for a series of cash flows to have >1 correct result for IRR.  Indeed, there will be as many solutions as there are changes in sign for the series of cash flows.

For example:

Today, -1,600
1 year from now, 10,000
2 yrs from now, -10,000

That has two solution, 25% and 400%.  Both of those IRR results are very gaudy, and simple rules like "make the investment if IRR >= hurdle rate" would have you pull the trigger on it, but I cannot think of anyone who would take that deal.

IMHO, the right answer to any kind of IRR question is, simply, "Don't!"

:)

Patrick
Avatar of dlmille
dlmille
Flag of United States of America image

Agreed for the most part...

I have to interject that IRR can be very valuable if used properly.  In the case of capital project assessment, understanding the discount rate at which NPV=0 is a very important (but not single) measure.  Generally capital projects I support have early cash out, then the rest cash in until termination (again, watch that termination value).  If the tools are not used blindly, they can be useful...

Dave
Avatar of rspahitz
rspahitz
Flag of United States of America image

that was fun...thanks for the diversion, Sid!
Avatar of dlmille
dlmille
Flag of United States of America image

Hey rspahitz - see my post (unfortunately, I didn't know Sid was already closing it, lol)?  I like your parsing technique, but think this alternative - using the precedents arrows - is the way to go.

Any flaws you see?

Cheers,

Dave
Avatar of rspahitz
rspahitz
Flag of United States of America image

Using the precedents feature seems pretty creative.
However, the result (from what I see by just looking at it) returns the cell name (which is good) but not the row number (although I guess that's easy enough to get.  Does it also return the range as individual entries (e.g. A3:C7 returns 3,4,5,6,7)?

The nice thing about a challenge like this is that it opens you up to lots of creative ways to get the job done.  I like to learn new ways to do things :)
Avatar of dlmille
dlmille
Flag of United States of America image

Yea - I just stopped when I got parent names and addresses, as the rest is left to Sid.  Easy enough to enumerate through a range to get rows, lol.  No parsing needed.

Yes - I learned at least 3 with this one.  I'd never studied the precedents property before.  Neat.

Dave
Avatar of Dave
Dave
Flag of Australia image

> understanding the discount rate at which NPV=0 is a very important

Not really "very" important.

Each asset has an appropriate discount rate that reflects the riskiness of the assets cashflows over a range of economic conditions. The IRR gives a pseudo break even discount rate that closes the arbitrage gap between the asset return and the expected return on a market and bond portfolio that mirrors the asset. This IRR rate doesn't though reflect the inherent characteristics of the asset, its a low level  significance "headoom" check

On top of the multiple solution issue caused by sigh changes
- IRR is easily biased by quasi financing whereas NPV isn't (ie leasing a car rather than buying it reduces the upfront spend making the project artificially better
- No scale
- It reflects only the internal return of residual cashflows in the project. An IRR of 20% doesn't give a 20% annual return unless you can reinvest the liberated cashflows at 20% ... hence MIRR is far more practical

Cheers

Dave
Re: http:#a36023261

Now THAT is what I call a lucid explanation.  I wish my finance profs could have channeled Mr Brett back when I was in university, because that explanation sure beats any such I ever got from one of them (and I had some good ones!).

Well done, Dave!
BTW, congratulations to teylyn for winning an iPad 2 in the "Badger stories" contest:

https://www.experts-exchange.com/blogs/EE-Team/B_5266-EE-Volunteer-Stories-we-have-a-winner.html
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Hurray!!! Congrats Harpy [ with a typo ;-) ]

Sid
Avatar of byundt
byundt
Flag of United States of America image

I just read through all the Badger essays, and thought they were nicely done and pretty upbeat as a group. But one of them was clearly better than the rest, and not by a narrow margin.

Congratulations teylyn! Woohoo!

Brad
Avatar of rspahitz
rspahitz
Flag of United States of America image

hey teylyn...can I borrow your ipad? :P
Actually, I'd like to know how you like it after you try it for a month
Avatar of dlmille
dlmille
Flag of United States of America image

>>Not really "very" important.
I disagree.  And we don't have to agree.  But your comment forces at least this one/last response, as I live with this stuff on a daily basis.

Each to his own.  Its one of several factors (if not important or very important it wouldn't be used), that are leveraged for billion dollar decisions.

If you don't like it, you can eat zorvek's shorts.  No mathematical explanations, please :)   I can't tell my CEO well, that's not important math, math, math. ...  I'll be run out of the board room, eating my shorts.

I will agree that NPV-0, IRR, etc., are just datapoints (not unimportant, nor very unimporant) - and why are we now nit picking the word "very"?...- most projects that go forward in my company meet all hurdles, and there's still scarce resource.  That's when strategy comes in.

So, I'll not show my finance pedegree if someone doesn't challenge mine, and mayhap not even then.  At the end of the day, its about strategy and beating the competition - rah rah!

Dave

Avatar of dlmille
dlmille
Flag of United States of America image

teylyn, geeze - I'm all goose pimpply from your badger story, really - nice job!

Hey, the iPad is a really neat toy - I wish I had one!

Dave
I WHAT?


... OMG ...

I have not even received an email about this. I'm just reading through the thread, trying to figure out WTF XIRR and MIRR might be and then someone congratulates me on winning the ipad2.

It's Friday night, just after 8 pm. I think I must've had one too many at beer o'clock. ...














I won?















really??




















Wow!


















<grin>
Ingeborg,
Top essay - beautifully put. And I didn't have to wait until the end for the verbs. ;)
Enjoy the iPad - my wife is practically a cyborg since she got hers.
Rory
Thanks, Rory.

I must confess, we already own a first generation iPad. I bought one last October. I had to smile when I read in Excelforum how all the blokes bought iPads for their wives, while I bought one for my husband. But I hardly ever get to use it, because either hubby or the boyz will have it in their firm grips. I love how versatile it is and I'm even willing to put up with iTunes in order to use it.

It's hard to get them in New Zealand right now. My company just raffled one away in an employee competition (which I did NOT win) and in order to honor the deal, they had to gray-import one from some obscure East Asian source...

There were so many great stories in the badger competition! It was an awesome read. I guess it was not easy for the jury to pick a single entry as the winner. If I ever make it to a badger meeting, I'll certainly bring the iPad and everyone can have a go! (altough by that time, iPad4 will probably be the rage).

Thanks all for your comments and for bothering to read the stories. Now, to all of you without a badge, get up off your seats, email AnnieMod and offer to volunteer for Cleanup duty.

It's worth it!

cheers, teylyn
To be honest, the only reasons I'm not addicted to one are:
1. My wife won't let me use hers.
2. It doesn't have Excel.

:)
#2 is a serious drawback. I'll consider spending my iTunes vouchers on Numbers ...


<still can't believe it>
Avatar of zorvek (Kevin Jones)

ASKER

Believe it. The iPad is based on the iPod/iTouch/iPhone operating system which is a lot different than the Macintosh operating system derived from the Next OS that Jobs brought with him. The Macintosh Office product generates revenue but pales in comparison to the Windows Office revenue. The Macintosh Office product has had a sordid life as it is. Remember when they tried to ditch VBA?
Avatar of rspahitz
rspahitz
Flag of United States of America image

congrats again, teylyn...oh, can I borrow your iPad 1?  wait...no Excel?  maybe OpenOffice.org.Calc?
and I heard that iPad2 doesn't require iTunes for setup...
Avatar of rspahitz
rspahitz
Flag of United States of America image

New direction...

What do you guys think about the idea of creating a new category called Excel Macros/VBA?
One of the issues that I see in this topic is that a lot of people want VBA answers and it's not "really" about using Excel but rather about using a separate tool to control Excel.
Many of us cross over on a regular basis, but I think it could help manage how to focus the attention for the users.  And, of course, sometimes our answer will be "why not try it without VBA" in the new topic or "you might want to try a macro" in this area.

Of course, there could be a similar topic for Word, but I don't think Word people use macros all that much compared to Excel people...

So?  New topic?
This has been discussed before, as I recall, and ruled out. They've only just finished whittling down the number of zones due to all the confusion. In my experience, it never works that well anyway.
It's been discussed before. One big Office VBA category has been suggested also. I personally like to keep the categories at a minimum so that askers don't accidentally ask questions in tiny, unmonitored zones (for example, any time a question is posted in the Theory zone alone, I think I may be the only one that sees it).
That and it's easier to win shirts.
Avatar of SiddharthRout
SiddharthRout
Flag of India image

>>>That and it's easier to win shirts.

Of all the T-Shirts I ordered, I just got 3... So I stopped ordering them... I have no faith left in the 'Delivery System' of EE... LOL

Sid
Avatar of Qlemo
Qlemo
Flag of Germany image

I won't trust in the Delivery System at your location - wasn't that behind the moon, then on the left? :P
Avatar of SiddharthRout
SiddharthRout
Flag of India image

I would agree with you had I not signed for the 3 packages... Plus Indian Postal service is not bad as you think they are :)

Strange... No one in EE noticed that I didn't sign for the other packages... :)

My biggest disappointment was I had ordered kid sizes. My daughter turned 1 today and I really wanted to give one to her. So I had ordered 3-4 pieces in kid's sizes and received none LOL

Anyways... It doesn't matter anymore...

Sid
Avatar of rspahitz
rspahitz
Flag of United States of America image

Thanks Rorya.
I had noticed that some of the Excel topic areas a really confusing and members seem to post in the wrong places because they can't find the ones they want.

For example, when I want to ask a new Excel macro questions, where should I go?  Well, these categories make sense to me: Software, Developer, Programming, with an afterthought being Microsoft.

So in Software, I see Office/Productivity located below Database, and nothing else matches the topics related to Excel, so I go into that and see: Office Suites and Spreadsheet.  OK, obviously that last one.  Whew!  There's Excel.  If I chose Office Suites, I'd also be able to get to Excel.
Each of these was a bit of a journey to get there, but at least I found it.  Frustrated users may have selected one of the higher levels just so the question can be asked without spending 5 minutes trying to find "Excel"

Now, if I thought I wanted to be an Excel "Programmer", then I'd go through maybe Project Management (nope! not there) or Misc Programming (which has no subsections) or (Ahha!) Prog Languages since I want VBA.
But no such luck...there's no VBA.  But there is Visual Basic Classic...hmmm, maybe that's it.  Oh, it contains VB Script...(and novice programmers may think that's it and get the topic wrong, or just pick VB Classic and get the topic wrong.)
Geez!  This would be a great place to link to that Excel VBA or Office VBA topic!  Or maybe "Classic" is good enough since they're really the same thing these days, although how many of us are monitoring that?)

If you were to try Microsoft, you'd find MS Applications or better yet, MS Development, which sounds VERY promising...until you look in there and only see Access, DotNet, and SQL.
So back to MS Apps and still no Excel or VBA but maybe MS Office, which offers, finally, Excel.

My point is that a lot of members will give up searching link after link after link for something where they already know what they want.  And when they don't find this very common category of Office VBA, they'll often just pick whatever's closest to wherever they are now (and get it wrong.)

I guess I'm just ranting because I think the topics of Excel and Excel VBA are as dramatically different as Dreamweaver and HTML coding.  One is about the application and one is about programming.
</rant>
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Thank You N :) However there is no need. I appreciate your gesture though.

Cheers

Sid
Rob, there'll always be folks who consider =A1+A2 programming and there'll always be posts in the wrong zones.

The fastest way for posting a question in the correct zone is to search for an existing answer about the technology you're using, e.g. Excel. Open a PAQ'ed question and click the "Ask a new question" button. The Q will automatically be zoned in the current zone.
Avatar of rspahitz
rspahitz
Flag of United States of America image

Thanks Badger, I mean Harp, I mean iPad winner, I mean, oh, I don't know what I mean any more.

And yes, =A1+A2 is programming, kinda like <HTML><BODY><P>I just programmed a web page</P></BODY></HTML> (although I guess formulas at least use real programming pieces like functions.)  But, of course, VBA is a **separate application** that just happens to interact with Excel so I think it justifies a separate topic.  I guess I won't win on this one.
Avatar of rspahitz
rspahitz
Flag of United States of America image

From this week's newsletter:

"One of the aspects of Experts Exchange we have always enjoyed is the spirit of collaboration among the Experts. justearth asked a question on a Saturday that involved copying information from a report and pasting it into a spreadsheet, but it didn't get much attention until Father's Day, when aikimark and dlmille started working on it: "Thank you. This make my life much, much easier. It appears to work swimmingly. If I have any follow-ups I will ask a related question.""

Good job guys! :)
Avatar of aikimark
aikimark
Flag of United States of America image

@rspahitz

VBA is an engine and run-time environment inside Office applications.  It is not a separate application.
Avatar of rspahitz
rspahitz
Flag of United States of America image

Well, technically, VBA is a dll that is included with each of the office products and, as far as I know the same dll that was used to create the VB6 application.
I call it separate because it launches its own window with its own title and works independently of each Office product but (because of the Office dll connection) has full integration with that piece also.
So I would call it separate but integrated, just like any other dll.  This one just suppose more independent functions than most dlls.
So is it inside? Sure, just like the charting module and probably dozens of others...but it's a world to itself as evidenced by the fact that you can build a Word module in the Excel VBA, export it, import it into Outlook, and it would still work without change and without Excel.  It's just looking for a host to do its things.  Of course, most of the functionality is designed to work on the current host, which then makes the code hard to use elsewhere.

Avatar of zorvek (Kevin Jones)

ASKER

What is important is how members model their projects and our site in their heads. And different members have different cognitive models at work depending on how they consume information and their role.

rspahitz makes a valid point but the proposed solution has been tried and it has failed miserably. At one point EE had close to a 1,000 zones made more confusing with mirrored zones in multiple disparate locations. There was too much granularity that caused more confusion than a less populous zone system.

What we need are better questions for the askers such as "do you want a formula solution or a VBA solution?" This serves two purposes: first it leads the asker down a path of self discovery before they drop their drawers in front of us, and it gives us more refined and important information with which to give better solutions in shorter order without guessing and without encouraging various multiple experts to post "In case you might want a ... solution..." with the asker ultimately faced with more confusion than they bargained for.

As far as splitting questions into smaller buckets such as Excel and Excel VBA, it doesn't make sense if we consider that a lot of work done here are blended solutions or solutions that morph from one form into another.

As far as creating buckets that somehow span multiple zones such as an Office VBA zone, it doesn't make a whole lot of sense when considering that VBA - as the asker AND expert perceive it - is very much tied to the application and not the language. There is much more crossover between Excel and Excel VBA and between Word and Word VBA than there is between Excel VBA and Word VBA.

So, technically, it doesn't matter what VBA really is as a chunk of code. But, to put my two cents into THAT rumble, VBA is NOT VB although the basic language syntax has a lot of overlap. VBA is a common managed code interpreter that is used by an application to, given that application's object model, manipulate that application's objects. All VBA code resides in the application's documents.

One could argue that there could be a zone that was just generic VBA but we already have that for the most part as VB.

Kevin
Avatar of rspahitz
rspahitz
Flag of United States of America image

>What we need are better questions for the askers such as "do you want a formula solution or a VBA solution?"

I think you're right.  Most members are looking for a solution, not which tool will give the solution (of course, many THINK they want something, like macros, even when a different option is better...that's the nature of the industry.)

And I agree that we have too many topics (and still do!)  One of the challenges of a site like this in trying to handle all of technology is there there are simply too many topics available.  Trying to manage those will be tough no matter how you do it.
On a side note, I remember having similar discussions here 10 years ago and I guess it's not really solvable: Do you give hundreds of topics to handle everything, or do you give just one topic, or some number in between.  I guess there's no optimal solution because sometime people want to know how to use EXCEL on certain HARDWARE across a NETWORK using VBA to talk to an iPAD, etc. and so they pick a topic or three and go from there.


Avatar of zorvek (Kevin Jones)

ASKER

Not everything has to be a 1 to n relationship.

Consider the above example. A typical user of Excel with a problem is probably not thinking about whether it is a VBA or a formula question, or if they ARE thinking about VBA versus formulas, it's probably because they tried a formula and couldn't make it work, or they tried some function and couldn't make it work. Bottom line: it's about Excel. No more, no less. And keep in mind they ask a lot fewer questions than we answer.

So they go to the Excel zone - an easy process with only one zone with "Excel" in the name - and now we have an opportunity to try a different methodology to extract some information out of them to make OUR lives easier and, indirectly, give them a more rewarding experience. Not by leading them down a path of additional zones with narrower scope, but by asking them questions that make them think about their problem in a non-stressful way. Gently pulling this information out of them with the allure of better solutions faster allows the experts to further refine their workload and thus gives the asker a more clear solution with less noise. And without the stress of fishing through a gazillion zones with nothing more than a few words as a cryptic name.

This is not an exercise in how to get the most points. It's an exercise in how to make all players' lives more rich and rewarding. Yes, it would be a perfect world if we could have just the right number of zones such that each zone received the precise number of questions per day we wanted to work and every question was right up our alley. We tried it and it didn't work because askers are the ones assigning the zones and they just want their damn problem solved as quickly as possible.

There are many ways to skin this cat. Unfortunately, the cat skinners are not the people writing the code behind the pages we use every day. So we end up with a lot of sub par functionality built by people trying to search engine optimize every page and cross marketing other potentially profit making opportunities while the people actually using the site suffer with the resulting mediocrity.

It's not a bad system but the potential for a better system is real and easily articulated by those who actually use it.
Avatar of rspahitz
rspahitz
Flag of United States of America image

I think you've convinced me...however, I think that maybe the problem is that I see a lot of Excel questions in the wrong zone (presumably because people are having trouble finding it).
Does EE have a "common topics" group that shows some of the more popular categories so that people can find them more easily?  I'd think that some of the more popular are DotNet programming, Networking, Web Page Design/Development, and Excel and Access.
If those are easier to get to when starting a new question, I think that would be cool.
Avatar of zorvek (Kevin Jones)

ASKER

>I see a lot of Excel questions in the wrong zone (presumably because people are having trouble finding it).

And this is a large part of the problem with additional zones. Askers have enough trouble with the existing ones.

EE has a very good solution that has been provided to them multiple times over the years but they have yet to implement. The solution is to improve the names by allowing zone advisors to actually edit the names, add detailed descriptions of what the zone is and how to best ask questions in that zone - again, zone advisor editable - and, finally, allow the zone advisors to add custom questions for the asker such as the example I describe above. This design has been very well documented and presented to EE on a silver platter a couple of times over the past so many years. Nothing yet although the effort did result in a drastic reduction of zones from the close to 1,000 we had a few years ago.
Avatar of aikimark
aikimark
Flag of United States of America image

As a zone advisor, I've complained that it isn't easy to find zones with a search tool. Most of my time as ZA is spent rezoning questions.  I feel your pain, folks.
Just two add my 2c opinion to Kevin et al good points:

Suggest the OP to start with a simple model and from there go stepwise towards what they want to solve.

First of all, all involved share the same simple model. Second, it also makes it possible for the OP to understand better the issue they want to solve.

Of course, the simple model approach could also be applied when OPs hit features/bugs in Excel.

Dennis
Avatar of aikimark
aikimark
Flag of United States of America image

nice article about what NOT to do with an Excel spreadsheet
http://www.techrepublic.com/blog/10things/10-ways-to-screw-up-your-spreadsheet-design/2562
Avatar of Qlemo
Qlemo
Flag of Germany image

Does not work for me :(
Avatar of zorvek (Kevin Jones)

ASKER

I haven't had an issue in 2007 or 2010. What version are you using? Have you installed all the updates?
Avatar of Qlemo
Qlemo
Flag of Germany image

2010 x86 on W7 x64, recent patch level, no hotfixes.
Some error with the callback functions for Ribbons is reported (twice), and no ribbon added.
Avatar of byundt
byundt
Flag of United States of America image

Among other things, SP1 fixes the error with SUMIFS, AVERAGEIFS and COUNTIFS that Kevin discussed above in http:#a35944588

The full list of fixes is available in a workbook downloadable from http://support.microsoft.com/kb/2460049 "Description of Office 2010 SP1"

Brad

[fixed link to comment, teylyn]
I usually wait to update with new SPs to see the first reports. However, this time I was eager so see the SP1 in action so I updated both my 32-bits and 64-bits versions. Guess what? They still work :-)

Kind regards.
Dennis
From the workbook describing all Service Pack changes in detail:

"In the Finnish version, "Serbia" is misspelled in the Language Options."

Phew, what a relief that this is finally fixed!
Yeah, I'm glad they spent their time on the important stuff and not on trivia like formulas not recalculating when they should...
BTW Chris - congratulations! You beat me to it after all. :)
Avatar of rspahitz
rspahitz
Flag of United States of America image

I wonder if the guy who fixed Serbia was the newbie @ $20/hour or the senior guy @ $75/hour...and I hope that when they fixed Serbia they didn't break Finnihs!
Avatar of byundt
byundt
Flag of United States of America image

Congratulations to new Excel MVPs and those who were reawarded. Most of Excel MVPs are awarded/renewed every July 1; I assume that the other quarterly anniversary dates apply to other disciplines.

Judging by their biographical sketches still being on the MVP web site, Rory, Patrick, Kevin and Dennis were all reawarded. Huzzah!

If you are a new MVP, please post in this thread (or send me an email if you are shy) so we can recognize your achievement. Microsoft does not publish the list--even though I asked nicely. The world at large doesn't learn of your achievement until you post your bio sketch here: https://mvp.support.microsoft.com/communities/mvp.aspx?product=1&competency=Excel&page=1

Brad
Yep - got the email about 10 minutes ago. The madness continues... :)
Maybe THIS year you will show up in Redmond.
Avatar of Runrigger
Runrigger
Flag of United Kingdom of Great Britain and Northern Ireland image

so guys/gals, how do you begin to obtain your MVP award from Microsoft?
Here is a start:

    http://weblogs.asp.net/rmclaws/archive/2005/04/03/396941.aspx

And then you have to be nominated by an existing MVP.

Kevin
Depends how much it would cost me to get there. I'll have two kids to support by then. ;)
Well then you should probably focus more on your paying job and less on messing around here making me accountable.

Nate would like some drinking buddies and I don't.
"making me accountable"
Huh? You lost me there.
Correcting my boo boos. And stop driving on the wrong side of the road.
A lot of the time I drive on the same side you do. :)
Ha!
Got my re-award message this morning.  For the curious: https://mvp.support.microsoft.com/profile/Patrick.Matthews

Sadly, Miriam Bizup (better known around here as mbizup) over on the Access side did not get re-awarded, so Kevin loses a jogging buddy at the next Summit.

(I jog myself, but I'm not crazy enough to jog with Miriam: she does marathons!)
I had applied for Excel MVP but got the Visual Basic instead :)

Sid
Avatar of rspahitz
rspahitz
Flag of United States of America image

Congrats to you all :)
That is fantastic news, Sid!  Woo hoo!
Thanks Patrick :)

Sid
Hi all,

Congratulations Sid and to You all who have been re-awarded!

In addition to what Kevin writes, You can also be nominated by a member of the Microsoft Excel team.

Take care everyone,
Dennis
Thanks Dennis :)

Sid
Avatar of rspahitz
rspahitz
Flag of United States of America image

So do these MVP designators give you anything other than pride?
>>So do these MVP designators give you anything other than pride?

In addition to the people's ovation and fame forever (well, not forever, you have to re-earn every year):
Free MSDN/TechNet subscriptions
As part of that, able to download a wide array of software for evaluation purposes
Invitation to attend the annual MVP Summit
Opportunities to have confidential interactions with the Microsoft product teams
Each year there is typically a gift as part of the award package

But don't discount the role of the pride :)
Avatar of dlmille
dlmille
Flag of United States of America image

Congrats to all!  Have a pint and think of the rest of us when you celebrate :)

Dave

>>So do these MVP designators give you anything other than pride?

Because we all are under NDAs we can tell You everything but then we must kill You, OK?

"Free" is somehow used in a wrong way. To get there most individuals must spend considerable time by supporting Q&A forums like EE.

It exist a subgroup in the MVP group; individuals who run blogs. In their case "free" is used in a wrong way.

Lately a new subgroup is slowly growing larger; MVP authors who spit out a couple of titles each year and are rewarded by Microsoft for writing books. In their case the word "free" is used correctly.

A top secret subgroup to which I belong to; doing nothing and for us the word "free" is something we always expect to get all stuff for.

Kind regards,
Dennis
Avatar of byundt
byundt
Flag of United States of America image

In case you were ever wondering what Sid looks like, he posted a snapshot in his MVP bio sketch: https://mvp.support.microsoft.com/profile=346171E4-C1A9-4989-BA48-1ECE558D6BC3

Congratulations Sid!
LOL

Thanks Brad :)

Sid
Some of EE members who already know how I look like...

1) David Miller (dlmille)
2) Ingeborg Hawighorst (teylyn)
3) Kevin Jones(Zorvek)
4) João Pinto (jppinto)
5) Patrick Arnold-Baker (patrickab)
6) Thomas (unsure of Full Name) (tommyszalapski)

Sid
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

What about me?
Yes you too Saqibh Bhai and many others :)

Sid
Avatar of rspahitz
rspahitz
Flag of United States of America image

>wondering what Sid looks like, he posted a snapshot
That can't be Sid...that's some movie star...just can't remember his name.
Sid,

VSTO? Now You surprise me a lot :-) Is VBForum still active, i.e. managed to port itself from classic VB to VB.NET?

Yes, also for me it looks like a movie star. A real developer never shows his/her face in daylight. After all, we don't want to scare the children.

Enjoy Your 15 minutes Sid!

Kind regards,
Dennis
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Thanks Dennis

It's almost 2 years now that I have been developing in VSTO. I have seen your blog though never got a chance to read the book which you have co-authored. :)

In fact I have something in my mind which I have been contemplating for quite sometime to discuss with you. Maybe now is the good time. ;-)

Sent you an email :)

Sid
Sid,

You're most welcome and if You also have the kindness to send me Your address I will see if the publisher can send a copy of the book to You.

Kind regards.
Dennis
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts