Excel Zone Expert Discussion, Number 20

So, do you come here often?

...and you are asking your question of whom?

.... nice ellipses

Well spotted ... groan!

Actually, it's an ellipsis.

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

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

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.

rfportilla, I come here five times on Thursday.

ttfn

Huh Kevin.

You just being homoepilliptic.

Dave

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?

> 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.

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!

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.

> 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?

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?

That's a terrible thing to say about Wayne .... of course you can't see his tractor from space!

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...

The tractor, from space:

http://maps.google.com/maps?q=-31.539089,146.785057&num=1&t=h&sll=-31.539142,146.785056&sspn=0.038043,0.06403&ie=UTF8&ll=-31.539241,146.785133&spn=0.00176,0.003484&z=19&iwloc=A

Isn't Excel great?!?!?

Is it time for ED21 yet?

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...

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

Surely those bullets should be tripled? ;)

Ahem ahem, am I in the Lounge?

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

Gang,

https://www.experts-exchange.com/questions/26499531/need-a-formula.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

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

@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?

>> 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.

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

eww, my friend's a vegetarian.  He's going to be upset when he finds out.

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?

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. :)

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.

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

Gang,For any of you who use class modules a lot, I'd love your feedback here:https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3802-Parent-Class-Builder-Add-In-for-Microsoft-Excel.htmlPatrick

A little humor at Kevin's expense:https://www.experts-exchange.com/questions/26532436/I-have-a-seven-column-table-that-needs-to-be-sorted-with-specific-criteria.html?anchorAnswerId=33868255#a33868255

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

Humor at MY expense?

I think this takes the cake:

"I am still working on this."

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.

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.

Don't worry - we specifically present each other in a negative light whenever we can.

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.

Thanks both for the reassurance. dmlyo50 is a better man/woman than me. He deserves his 500 points if he ever sees them.

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!!

Hey Stephen,
temerity.. injunction.. jocular
We don't all speak British you know!

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.

Folks

Can anyone advise why some code would keep stopping when triggered.  I am referring to https://www.experts-exchange.com/questions/26406252/Need-help-with-this-macro-code.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

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

https://www.experts-exchange.com/questions/26538336/Harlan-Grove-PULL-and-VLOOKUP.html

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

I share Stephen's dyslexia. I too initially read cyberkiwi as cyberwiki - just makes more sense


So ..... is there a dog?

Then there was the dyslexic devil worshipper that sacrificed animals to Santa.

Dyslexics have more fnu.

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!

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

https://www.experts-exchange.com/questions/26542634/Adding-in-data.html

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

cheers, teylyn

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.

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

I do - that's why I posted it here. :)
Hey ho, once more unto the breach, dear friends.

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???

You need to use a format of: hh:mm:ss.00 rather than hh:mm:ss.ms

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

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.

https://www.experts-exchange.com/questions/26550427/Word-2007-suddenly-shows-me-strange-characters-How-do-I-get-rid-of-them.html

Teylyn ...

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

Chris

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:

https://www.experts-exchange.com/questions/26550427/Word-2007-suddenly-shows-me-strange-characters-How-do-I-get-rid-of-them.html?cid=1576

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?

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

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?

:-))

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

>> "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!!

Communication breakdown?https://www.experts-exchange.com/questions/26567932/MS-Excel-Difficult-formula.html

Easy points for people who can do grouping with VBA code...https://www.experts-exchange.com/questions/26568397/Excel-2003-how-to-GROUP-rows.html?anchorAnswerId=33984926#a33984926

I posted to both.  What the heck?  I can't break it if its already broken...

@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?"

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"?

@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?

@rfportilla, you posted a 1.2 MB screenshot in https://www.experts-exchange.com/questions/26568397/Excel-2003-how-to-GROUP-rows.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

@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

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.  

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.

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.

@33987742
Interesting thought.
With questions getting answered in 2, it gets interesting if you retract an answer after it has been accepted!

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

Pandora is knocking at the door.

Never say never again...

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

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

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?

(Besides, Netminder only has 1,612 points.  How much could he possibly know???)

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.

Hi all,

Cavalry needed at https://www.experts-exchange.com/questions/26569167/Excel-macro-question-on-importing-data.html?cid=1572&anchorAnswerId=33997939#a33997939

thanks, teylyn

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

> 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!   :)

You like when I'm a little bitch, don't you...

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

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

>> 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

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".  :-)

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...

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!

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:https://www.experts-exchange.com/blogs/matthewspatrick/B_3355-Possible-Future-Article-SendItem-class-for-Outlook-automation.htmlCheers,Patrick

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

I guess you mean this one: http:/Q_26583296.html
OP didn't seem much taken with a non-VBA answer. ;)

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?

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.

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...

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

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 :)

EVALUATE has to be used in a defined name, as I said. you cannot use it directly in a formula anywhere in a sheet.

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.

This thread is starting to get rather long and slow to load. Can we start the next one, please?

thanks brad ;D

Teylyn gets her wish: http:/Q_26587770.html