Solved

Excel Zone Expert Discussion, Number 20

Posted on 2010-09-22
127
846 Views
Last Modified: 2012-05-10
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:

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

Previous Expert Discussion: http:/Q_26304691.html

Next Expert Discussion: http:/Q_26587770.html

---

Zone Advisors

When this thread reaches a page size of about 75K (about 150 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.
0
Comment
Question by:zorvek (Kevin Jones)
127 Comments
 
LVL 9

Expert Comment

by:rfportilla
ID: 33741188
So, do you come here often?
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 33741202
...and you are asking your question of whom?
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33741244
.... nice ellipses
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33741261
Well spotted ... groan!
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 33741277
Actually, it's an ellipsis.

Does anyone know when a four period ellipsis is used versus a three period ellipsis?
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33741289
maybe when it's used by an epilliptic? Wikipedia says, it's at the end of a sentence, though.

http://en.wikipedia.org/wiki/Ellipsis
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 33741305
Hey! Dave, you gonna let some Kiwi smarty pants insult you like that?

Dang, now that would be cool: New Zealand and Australia getting in a brawl.
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 33741314
rfportilla, I come here five times on Thursday.

ttfn
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33741318
Huh Kevin.

You just being homoepilliptic.

Dave
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33741321
That's not only cool, that's a must! Deeply ingrained in the respective cultures. Slap the other one wherever you can! Take no prisoners. Right, Dave?
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33741335
> That's not only cool, that's a must! Deeply ingrained in the respective  cultures. Slap the other one wherever you can!
Dead right

But when it comes to the rest of the world, its ANZACs all the way

Although underpinning the sheep shagging and underarm jokes aside its a very healthy respect. Kiwis are seriously tough characters.
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 33741350
Dave, you're acquiescing already! What do they have, a few bottles of wine and a sheep or two? You have Kangaroos, deadly snakes, and that drunk farmer with the bad ass tractor! You need to kick some ass!
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33741359
Dave, and without Oz, the Kiwis wouldn't be able to run their country. As an immigrant, I probably don't understand the full extent yet.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33741372
> a few bottles of wine and a sheep or two?
But thats all we have
plus a reef, a big rock in the middle of nowhere and a few quarries for China

> You have Kangaroos, deadly snakes, and that drunk farmer with the bad  ass tractor
Actually I'm intrigued by the drunk farmer with the bad arse tractor - what exactly is that you're talking about?

0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 33741379
Who do you know who lives on a farm, drives a bad ass tractor that can be seen from space, and is drunk most of the time?
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33741393
That's a terrible thing to say about Wayne .... of course you can't see his tractor from space!
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 33741415
Most farmers I know are drunk most of the time. But I'm not a farmer, so I don't know what my excuse is...
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 33741434
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33741465
Isn't Excel great?!?!?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33741749
Is it time for ED21 yet?
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 33743534
Dave,
Here is the latest update on commonwealth games...and trust me you not going to like it.
  • Politicians in my country spent million of rupees in the name of "common wealth" and the end result was nothing. The fixtures and the places are still not ready completely and I'm just wondering where those millions of rupees have gone. Trust me, because of this commonwealth the corruption was at all time high.  
  • It's been raining here madly and some parts of Delhi are already affected by flood. Because of this rain and plus other health hazards like dengue, malaria because of mosquitoes will be all time high.  
  • Due to commonwealth, we already have closed 1 side of the road reserved for commonwealth--and the roads are 2 lanes--so net result only 1 lane of road available to common person, resulting in jams like anything.  
  • The inflation i.e. food prices, petrol prices is at all time high because they need money to support CWG games, which is going in their pockets.  
Net result at the moment is that we are still not prepared for it because of all-time corruption which happened here and CWG right at the corner and it's only because of our corrupted politicians who treated the money as their personal money.
Saurabh...
0
 
LVL 80

Expert Comment

by:byundt
ID: 33743882
There was a sale on punctuation at the grammar wallah, so I added liberally to the preceding comment. My apologies if these changes misconstrued Saurabh's message.

Brad
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33743929
Surely those bullets should be tripled? ;)
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 33744247
Ahem ahem, am I in the Lounge?
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 33745382
Thanks brad..and when I read it myself again, I see lot of misses myself and i see what you are trying to say.
lol@Mahrish..Visit delhi during CWG you will know what i'm talking about..I forgot just to add all the maid servants are gone back to there villages because of the additional secruity measures so you land up with no maids at all.
Plus we had recently fresh attacks on Jama majid in delhi which resulted in increase secruity measure now when you travel to and fro to certain places where you are frisked like anything
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33770954
Gang,

http://www.experts-exchange.com/Security/Vulnerabilities/Q_26499531.html

Despite the link, it is really an Excel question :)

Brad and I are in there, with very different approaches.  Anyway, I have tried several different times to explain the PivotTable approach to the Asker, and there is a communication breakdown somewhere.  Can someone else please tag in?

Thanks!

Patrick
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33787698
Hi all,

this may have come up before, but even if it has, I feel it may need some more attention.

With so many different versions of MS Office in active use right now, it's sometimes hard to provide an asker with a useful answer unless the version of MS Office is provided.

For Excel, it is new functions, pivot table behaviour, charting behaviour, conditional formatting, heck, in 2010 even data validation follows different rules, when it comes to referencing other worksheets. And, of course, the differences in the Object Model, the macro recorder, etc.

Similar significant changes have happened to Word, PowerPoint, Outlook and other products of the MS Office Suite.

Eric, Kevin, Brad, and the friendly mods: could you please investigate what it would take to implement something like a mandatory tag to identify the Office version in use by the asker when they place a new question in one of the MS Office sub-zones?

Especially after 2010 went retail, I've come across so many questions where a lot of sweat was poured into a suggestion, only to find that the asker was using an Office version for which the (apparent) perfect answer just did not gel.

Some askers, despite using 2007 and later, still post their files in compatible format. Upon opening the attachments, there may be clues to tell that they are using one of the later versions, and the rest is a no-brainer. But it's not always that easy.

I realise that MS Office is just one part of EE, and that a system change to implement a mandatory version ID may be hard to implement. But maybe it's an issue in other zones, too. ??

Or maybe it would be possible to create sub-zones for different Office versions?  There are a dozen different zones for Windows OS. I don't think we need to go overboard, but just two different zones for 2003 (and younger) and 2007 (and older) would certainly make a difference. The existing filter system would allow experts to stay on top of all existing version zones, while at the same time enabling them to pinpoint whether or not you can use Sumifs or more than three CFs.

For points accumulation, it might even be possible to sum the individual expert's points for 2003-and-earlier and 2007-and-later to arrive at a total Excel/Word/Powerpoint/Outlook points balance.

After all, the EE system is just a piece of software and it should be possible to configure it to apply certain rules and parameters <do I need to duck?> (I'm sure there are experts on this site who would have suggestions how to achieve this in the cron jobs or whatever the things are called that add up our points).

I don't have a clue what impact such a change would have on the overall EE zone system, but I sure feel that both askers and experts would benefit from a system that eliminates guesswork regarding the version in use, and hence the suitable solution for a question.

There now. This has been bugging me for a while.

Netminder, you are probably bombarded with requests similar to this one. :-))  But the Office landscape is changing. Excel <> Excel. Word <> Word. Powerpoint <> Powerpoint (especially!!). I feel we need to address and reflect this at EE to provide better service to the askers and avoid wasting experts' time.

cheers, teylyn



0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33790707
@Teylyn: With regard to your frustration, the posters don't always know how to communicate their questions correctly.  As an IT professional, it is a large part of my job to probe to find out what the end user actually needs.  Often they are using the wrong tool.  Many of my first posts to someone is "what are you trying to do?" or "please clarify."  I believe that we have too many zones as it is.  Especially for someone like me, and many others here, who can answer questions in many areas, it is counter intuitive to have even more zones to try to keep up with.  I would hate to see more zones added.  Instead, having a probing question such as "What version are you using?" during the question submission phase might be helpful.  This would guide the user to providing the information and should not be difficult to do in terms of modifying the website.  

Just out of curiosity, does anyone know what this site was built on?  Is it all custom or is it using a CMS engine?
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33792479
>> if there is ever a disruption in the supplies of bubble gum and duct tape

Ah! This matches the Kiwi approach. Number-eight wire ...

Thanks for the insights.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33796174
I've just sent a cage of our national birds to EE, along with instructions on how to prepare them for the offering: http://marbella.to/humour/dec00/kiwi.jpg
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33798349
eww, my friend's a vegetarian.  He's going to be upset when he finds out.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 33800589
Teylyn bring up an interesting question.  I notice that SQL Server has it's own zones based on the latest releases.  2005, 2008, etc.  How come certain zones break down by version and others don't?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33801044
I have to say that everywhere I have seen attempts to use different forums for different versions, I don't think it works terribly well. Most users don't seem to be aware that there are differences (most of the people I work with don't know what version they have anyway), or they want something that will work in several (or all) versions. Too much hassle for not much reward, IMO. Fine to put the version box in the question wizard, but I wouldn't expect too much from it. :)
0
 
LVL 80

Expert Comment

by:byundt
ID: 33801767
We are probably close to the tipping point (or perhaps even past it) where more than half the Askers are using Excel 2007 or later. I've started responding with Excel 2003 suggestions only when file type, question tag or other clues make me suspect that Asker is using an older version.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33801998
I usually just ask. It can be quite instructive when they say they're not sure: gives you an idea how much hand holding will be involved. :)
Eric,
Nice to know Kevin's been doing something all this time - I thought he was on a long holiday...
Rory
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33836680
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33868455
0
 
LVL 80

Expert Comment

by:byundt
ID: 33879504
There is a très cool use of Google API to translate from Japanese into English in a user-defined function written by Gautam (gbanik) in http:/Q_26533042.html#a33878384   He had to break the lengthy text up into smaller fragments, but was able to produce very plausible medical descriptions in the translation. The same approach applies to many other language pairs.

Brad
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 33879639
Humor at MY expense?

I think this takes the cake:

"I am still working on this."
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33880204
I hadn't found this thread before. So this is where you all hang out. What I love about the question referred to by Patrick is that the OP posted quite possibly the most complicated problem ever posted at an Excel forum with a single line and no attachment and then had the temerity to be offended by a pretty benign post asking him/her to explain aforementioned unbelievably complicated problem.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33880213
Just realised I may be banned for life as have broken one of the injunctions above. I was being jocular, but will take it on the chin if I am banned or reprimanded.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33880239
Don't worry - we specifically present each other in a negative light whenever we can.
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 33880251
Let's see:

- Presenting any specific member in a negative light - Did we paint him in a negative light? Na. He's just trying to get something done and isn't in a good mood.
- Suggesting that any specific member needs to change their behavior - I didn't, did you?
- Suggesting that others blacklist any Asker for any reason - Never happened. But I'll tell everyone right here and now, I am not going to touch THAT question.

You're good.

One has to wonder what dmlyo150 is thinking about now.

Dave, go chase a Kangaroo.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33880272
Thanks both for the reassurance. dmlyo50 is a better man/woman than me. He deserves his 500 points if he ever sees them.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33880326
Here I am sitting here waiting for OP to click on Request Attention so that I can roll up my sleeves and jump on the opportunity for 200 bonus points!!
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33880334
Hey Stephen,
temerity.. injunction.. jocular
We don't all speak British you know!
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33880383
cyberwiki: I have got off to a good start. What I meant was he's got a lot of bleedin' nerve, but I was only joshing and didn't mean to break any of the rules. Straight up. It's late and I blame the insomnia.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33881829
Folks

Can anyone advise why some code would keep stopping when triggered.  I am referring to http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_26406252.html and yes it's outllook but it's about why the event triggered code will keep breaking execution in the VBE so perhaps your skills can help here.

Have checked for references being missing already, (i've seen that before).

Chris
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33888524
If anyone has any familiarity with Harlan Grove's Pull function, perhaps they'd care to visit this one:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26538336.html
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 33890255
Stephen,
cyberwiki?
Was that deliberate.....nice......? - given ck's extensive knowledge I think I too will be referring to him as cyberwiki from now on.......
You lost me with "bleedin' nerve" - I think you mean "flaming cheek" old bean, what what?
barrington
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33890265
I share Stephen's dyslexia. I too initially read cyberkiwi as cyberwiki - just makes more sense


So ..... is there a dog?

0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33890312
Then there was the dyslexic devil worshipper that sacrificed animals to Santa.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 33890490
Dyslexics have more fnu.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33891788
Not forgetting the dyslexic, insomniac atheist who lay awake at night wondering if there was a dog.

Oops, wish I could say it was deliberate but it wasn't. Sorry cyberKiWi!
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33902425
Hi folks,

it's too late at night to get my head around this. Probably a combo of Barry's time conversion magic and some VBA

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26542634.html

I can't resolve the milliseconds in column E. The rest should be straightforward.

cheers, teylyn
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33902484
I'd use the equivalent of:
activecell.value = application.text(activecell.Value, "00\:00\:00\.00")
then set the format of the cell as required.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33902498
Rory,

thanks.

Happy for you to play this out in the thread. I'm about to crash for the day.

You know the customer and what you'll get into.

cheers
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33902508
I do - that's why I posted it here. :)
Hey ho, once more unto the breach, dear friends.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33902543
but why does it result in a value of

00:01:00:10 ?????????

I thought it was me being blonde, but since I'm more gray than my previous auburn, that can't be it.  How to get at a hh:mm:ss:ms value of 00:01:00:00 ?  

These customers seem to work in media and news, so milliseconds DO matter. How to get a clean zero on the milliseconds, instead of a dirty ...:10???
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33902597
You need to use a format of: hh:mm:ss.00 rather than hh:mm:ss.ms
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33902627
That's what I started to point out in the thread, but then the late hours got the better of me. Some days I'm good until after midnight. Today, not.

Thanks for jumping in.

cheers, teylyn
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33927126
OK, this is not in the Excel zone, but I thought it was a beautiful example of how  - instead of experts' expertise - it's more often experts' patience that is tested.

I only saw this after three or four answers had been posted. I'm not sure I would have been so patient.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_26550427.html
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33927203
Teylyn ...

Are you sure of your link .... discussions of patience on a timeline 06:59 - 07:16 doesn't work for me?

Chris
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33927222
Chris, when I click the link, I get to a post about a "backward P signs littering the screen in Word"

here's the link again:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_26550427.html?cid=1576
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33927241
I'm not sure what you are driving at teylyn.  I am guessing (only); is it that the lack of proficiency would have driven you nuts such that you want to hammer out the solution completely in caps rather than patiently explain the situation?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33927289
Sorry then as it's the question indeed but with just a few posts in such a short space of time and a spread of posts covering what as well as how I really don't see how patience comes into it so i'll bow out.

Chris
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33927309
Cyberkiwi,

I probably would not have answered at all, in fear of answering in an inappropriate way.

Show/hide paragraph marks in Word is such a basic thing that no user should be phased by it after opening Word for the third time in their lives. Formatting is so important in Word display that working with (i.e. switch on/off) formatting marks is a must if you want to understand what's going on in your document.

Well,  I'm probably too safely installed in my ivory tower. That's why I also bristle at people using

=sum(A1+A2)

It's just so sad to see that many people apparently do not even receive a basic training on the features of the tools they are expected to use in their daily work.

Well, I guess that's what's makes places like EE thrive. But don't the bosses understand how much more efficient their staff would be with some basic training? All the time spent hunting down a solution on the net for stuff that could have been explained in a 1-hour 101.

As it is,  for experts it's sometimes a challenge to figure out if people are suffering from a major case of RTFM or if the issue goes deeper.

So, challenges on both sides - a win-win situation, right?

:-))

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33927394
Understand now, but of course management have multiple reasons for staff selection and all too often cost effictiveness over the whole life cycle is overlooked in terms of the short term.  

Recall that with bloat all these applications have 10 times the functionality any individual wants because everything that might be useful is included, except those that I want ;o)

S I guess i'm simply reminding you ... as I have seen you say before "the only stupid question is the unasked one". i.e. If you haven't knowingly selected the hide/show option and neve seen it in use before what can you do?

Chris
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33927435
>> "the only stupid question is the unasked one"

Good of you to remind me, Chris. I should know better, what with my hubby freaking out at me installing Office 2010 over Office 2000 and now he can't find his favourite commands.

Note to self: put yourself in the noob's shoes, teylyn!!
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33984146
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33985104
Easy points for people who can do grouping with VBA code...http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26568397.html#a33984926
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33985530
I posted to both.  What the heck?  I can't break it if its already broken...
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33985553
@Teylyn I dunno.  I've heard some pretty dumb questions that should have never been asked.  For instance, after ordering french fries: "Would you like fries with that?"
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33986989
Is this a stupid question or question for a stupid person?

"the only stupid question is the unasked one"

I may not be British, and I don't know what the grammar is like among the schoolboys there nowadays, but my question/s go/es,

What is this "unasked (one) question"??
It is a magical special catch-all question?
Or was that supposed to be "... are the unasked ones"?
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33987142
@Netminder the person deals with Access mainly, I think.  It was spawned from that.  It should be in Excel, but it should be close to done now.  I don't care anyway.

Stupid question: how do you unask a question?
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33987198
@rfportilla, you posted a 1.2 MB screenshot in http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26568397.html

Can you please save your screenshots as GIF, JPG or PNG, which are a LOT smaller than BMP. For one, they will upload faster, and secondly, people won't have to consume so much traffic when merely looking at a question. I hardly ever download a file attachment larger than 200 KB, because I'm very conscious of my data cap.

Please don't force people to download Megabytes because of oversized in-line images.

cheers, teylyn

0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33987500
@Teylyn Yes, I deserve to be quartered for such a noob mistake.  I was working quickly and didn't pay attention.  However, I would not normally do this.  :-p
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33987504
Btw, is there a way to delete your own comment?  And why not?  I would have liked to undo that 1.2 MB post, but obviously I can't.  
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33987523
You can't edit your comments at EE, nor delete them. You can request attention and ask a moderator to make changes, but I wouldn't worry too much, now that the question is about to close.
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33987742
Some forums allow deletes for a time period like 15 mins.  Usu. you can decide whether you meant to say something in 15 mins and not cause issues across species.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33987804
@33987742
Interesting thought.
With questions getting answered in 2, it gets interesting if you retract an answer after it has been accepted!
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33987967
2 Possible issues w/answers

1. Comment is retracted after awarded.  I would assume the expected behavior is to disallow removing any comments that have been awarded points at the time of acceptance.  
2. Comment is awarded points after being retracted.  If the commenter retracts the correct answer b4 points are awarded, then sucks to be him/her.  (S)He must repost and hope to be awarded or asker can do as (s)he pleases.

JM(H)O

0
 
LVL 50

Expert Comment

by:teylyn
ID: 33987992
Pandora is knocking at the door.
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33989127
Never say never again...

Besides, this doesn't have anything to do with Excel, does it?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33989469
Yikes

"Besides, this doesn't have anything to do with Excel, does it?", On a quick look at Netminders page it says:

"Senior Administrator at Experts Exchange"

Here's hoping your account doesn't spring the electronic equivalent of a curse for suggesting they don't know what they're talking about
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33994651
lol, I'm not suggesting that they don't know what they are talking about at all.  I do, however, respectfully, disagree. I "think" it can be managed.  And "Never say never again" is an old expression.  Basically, you shouldn't tempt fate.  As soon as you say "I will never...," you'll find yourself doing it.  It's the great law of probability.  As per "The Hitchhiker's guide...," the less likely something is, there is a mystical reverse effect that makes it more likely.  

Lastly, "this doesn't have anything to do with Excel" is an attempt to end the discussion (since it has gone nowhere) so that we can continue discussing the topic of this post, Excel.  Right?
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33994686
(Besides, Netminder only has 1,612 points.  How much could he possibly know???)
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 33994746
He says he knows a lot. He's actually a pretty bright guy - just seems to spend most of his time beating his head against walls when he isn't cooking and doing laundry.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33998185
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33998956
Time for some fun and games.

Tracy, better known to some of us as broomee9, is poised to become the 19th member of the Excel Genius club; at her present pace, she should hit the milestone in 2-3 weeks. That got me thinking about who else may be creeping up there.

The leaderboard below is very simplistic. I basically:

1) Took all Experts with totals >=500k and <1 MM
2) Added in any Expert from the YTD Top 25
3) Removed inactive Experts, like ture

Now, the "projected date" is just a bit of flummery, built on assumptions. No doubt someone we've never even heard of yet will burst onto the scene and shake this up, and other Experts will speed up or slow down their paces. But it's fun to see what's coming.

We have an interesting race brewing between cyberkiwi and spattewar: spattewar has a large lead, but cyberkiwi's frenetic rate of answering may just allow him to sneak past.
Excel-Genius-Watch.bmp
Excel-Genius-Watch.xls
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33998979
> He says he knows a lot. He's actually a pretty bright guy - just seems  to spend most of his time beating his head against walls when he isn't  cooking and doing laundry.

Ouch. You can be a right bitch Kevin!   :)
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 33999201
You like when I'm a little bitch, don't you...

Patrick, me thinks you have a little too much time on your hands ;-)
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33999439
Go, Tracy!! I'm waiting for you!

Although, I must say, the "club" rooms are fairly non-existent (at least as far as the club members have let on to me), and they definitely don't have a Ladies' room there. Bring your own bucket.

Let's start the change. Keep at it, girl!

cheers, teylyn

0
 
LVL 50

Expert Comment

by:teylyn
ID: 34000251
>> telephone systems, plumbing and recalcitrant furnaces

Don't we all love a day away from office work?

NZ is seriously starting into the summer season. Anyone from the Northern Hemisphere planning a time out in the NZ summer is welcome to contact me for anything from insider tips to a stay on my couch. Oz farmers and weird creatures are included as well, as long as they bring sufficient amounts of alcohol.

cheers, teylyn
0
 
LVL 24

Expert Comment

by:broomee9
ID: 34002857
Thanks guys, I'm trying.  Almost there, <30k now.  My three goals for the year are:
1.  Become an overall genius - Done
2.  Become and Excel genius - Need <30k
3.  Get a million points in Excel for the year - Need about 115k
Now I'm shooting for 11/10/10 for goal 2 to make Patrick's predicition come true.
I think they should give you a genius shirt once you become an overall genius (look how many millions cyberkiwi had before he became a genius in a specific zone).  That way, if you only get genius in one thing, you can still wear the shirt and not worry about ruining it, since you'll have a backup.
 
I have my bucket ready, ladies room or not, I still want to be in the "club".  :-)
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 34015934
Patrick,

Nice analysis...And i guess im out of the league for this year till the time i got a backup for me on my workplace because this year has been like too hectic for me where i have been spending on a average 12 hours at work and 2 hours in commuting and rest 10 hours to sleep/eat/do other set of activities.

Saurabh...
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34015995
Okay just for kicks, it looks like there will only be 5 in-the-year Geniuses this year.
The race for the 4th one goes something like this (completely unscientific, utterly crap analysis based on one single day)

      4. barryhoud…967,131
      1,000 Expert Points Yesterday
Time to Genius: 33 days
     
      5. broomee9 885,693
      9,800 Expert Points Yesterday
Time to Genius: 12 days

Ready, get set, go!
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34037617
Gang,For folks who sometimes automate Outlook from Excel or other Office apps, I would appreciate it if you could check this out, and offer your feedback:http://www.experts-exchange.com/blogs/matthewspatrick/B_3355-Possible-Future-Article-SendItem-class-for-Outlook-automation.htmlCheers,Patrick
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34046751
Hi gang,

I lost track of a question posted maybe yesterday that was something like this...

Help me device a sheet that has names of hotels in one sheet and room types (with respective rates) in another. User would select a hotel and the room on the Input sheet and the selected entries must get displayed separately on the same sheet.

Since I didnt participate in it (didnt have time then), I am not able to trace it now as it may have been closed.

I was intrigued enough by it that I went ahead and spent some time.

An automated "indirect" macro-free solution could have been the way to go.

I am attaching my version for reference.

I would also like u folks to see if the validation formulas could be optimized as it has become bit lengthy.
Hotel.xlsx
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34047080
I guess you mean this one: http:/Q_26583296.html
OP didn't seem much taken with a non-VBA answer. ;)
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34047190
Thanks Rorya for your response. I hoped that he would accept a non VBA solution :)

Meanwhile, did u get a chance to actually work it out?

As mentioned, I got too interested in it and worked on the above attached file. Though pleased with the output (all automatic including dimensions and addresses), the formula got too lengthy.

An interesting observation... the INDIRECT validation formula did NOT work with automatic resized ranges.

Any comments? Also improvements?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34047285
INDIRECT doesn't work with dynamic ranges - you have to use the old XLM function EVALUATE in a defined name to work around that (at least, that's the only way I know of). The rest is all done fairly easily using Debra's tutorial.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34047341
I was determined to make it work using INDIRECT hence

=OFFSET(INDIRECT(SUBSTITUTE(SelectHotel," ","")),,,COUNTA(INDIRECT("Room!" & SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(SUBSTITUTE(SelectHotel," ",""))),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(SUBSTITUTE(SelectHotel," ",""))),4),"1",""))),1)

phew!

by the way, how would you use EVALUATE in a formula? Or maybe you are referring that it cannot be done through a formula...
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34047383
Rather than having the DV list set to say =INDIRECT(A1), you would define a name (say RoomList) using =EVALUATE(A1) and then use that name as the DV source =RoomList
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34047500
1. Am not able to figure out how to use EVALUATE. In the DV? As a formula? Is the function Excel version specific? I remember using it sometime in an earlier version of Excel. I use 2007 currently.
2. Regarding using a dummy name... I deliberately tried to avoid using any supporting names or cells... though my lengthier method does not have any visible advantage :)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34047521
EVALUATE has to be used in a defined name, as I said. you cannot use it directly in a formula anywhere in a sheet.
0
 
LVL 80

Expert Comment

by:byundt
ID: 34047539
EVALUATE must be in a named formula. You create it in the Name Manager just like it is a named range.

EVALUATE won't work at all in data validation or worksheet formula. And if you use it, you will still get a macro warning message.
0
 
LVL 13

Accepted Solution

by:
gbanik earned 0 total points
ID: 34047576
rorya! learned something new today :)
thanks :D
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34047587
This thread is starting to get rather long and slow to load. Can we start the next one, please?
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34047592
thanks brad ;D
0
 
LVL 80

Expert Comment

by:byundt
ID: 34047644
Teylyn gets her wish: http:/Q_26587770.html
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now