About Us

MJW[_2_]

Thanks! Works like a charm.

Much Appreciated,

Jamie

"Elkar" wrote:

Sure, if I understand correctly, you could use:

=IF(RIGHT(A1,2)="yr",LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1))))),"")&IF (NOT(ISERROR(SEARCH("ASP",A1))),"A","")&IF(NOT(ISE RROR(SEARCH("Basic",A1))),"CC","")&IF(NOT(ISERROR( SEARCH("Premium",A1))),"CC+","")

HTH,
Elkar

"MJW" wrote:

Elkar,

Thanks! Is there a way to append values such as "A" for any ref cell with
"ASP", "CC" for any ref cell with "Basic" or "CC+" for any ref cell with
"Premium"?

Thanks Again,

Jamie

"Elkar" wrote:

I just posted a response to your previous post.

http://www.microsoft.com/office/comm...xp=&sloc=en-us

HTH,
Elkar

"MJW" wrote:

Ok, while this may be an easy way to induce contempt, notoriety and maybe
even a few death threats, can anyone see anything wrong with this formula?
Yes, I probably could have used ranges, I know. At any rate, I can't seem to
figure out why this doesn't work, and considering the fact that the formula's
more painfully-long than it is complex, I'm pretty sure I'm just missing
something obvious. All the character ref lengths are accurate, and I didn't
see anything off with the parentheticals, so I'm sort of befuddled as to
what's causing the formula to fail.

=if(right(p2,2)=€yr€, if(or(left(p2,7)=€ASP1 yr€, left(p2,7)=€ASP 1yr€,
left(p2,8)=€ASP 1 yr€), €œA1€, if(or(left(p2,9)=€Basic1 yr€, left(p2,9)=€Basic
1yr€, left(p2,10)=€Basic 1 yr€), €œCC1€, if(or(left(p2,11)=€Premium1 yr€,
left(p2,11)=€Premium 1yr€, left(p2,12)=€Premium 1 yr€), €œCC+1€,
if(or(left(p2,7)=€ASP3 yr€, left(p2,7)=€ASP3 yr€, left(p2,8)=€ASP 3 yr€),
€œA3€, if(or(left(p2,9)=€Basic 3yr€, left(p2,9)=€Basic3 yr€,
left(p2,10)=€Basic 3 yr€), €œCC3€, if(or(left(p2,11)=€Premium 3yr€,
left(p2,11)=€Premium3 yr€, left(p2,12)=€Premium 3 yr€), €œCC+3€,
if(or(left(p2,7)=€ASP5 yr€, left(p2,7)=€ASP 5yr€, left(p2,8)=€ASP 5 yr€),
€œA5€, if(or(left(p2,9)=€Basic 5yr€, left(p2,9)=€Basic5 yr€,
left(p2,10)=€Basic 5 yr€), €œCC5€, if(or(left(p2,11)=€Premium5 yr€,
left(p2,11)=€Premium 5yr€, left(p2,12)=€Premium 5 yr€), €œCC+5€))))))))), "")

If anyone figures this out and is in the northeast, I'd be more than happy
to buy you a drink. Or several. (I may entertain the notion of giving you
my firstborn child, too.)

Thanks,

Jamie

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
tree chart	Quant	Charts and Charting in Excel	0	March 5th 07 02:44 PM
why do all excel worksheets/workbooks close when I close one?	Penny	Excel Discussion (Misc queries)	1	November 29th 06 03:49 AM
Forest Plots......can these be done in excel or powerpoint	Claire8	Charts and Charting in Excel	8	October 14th 05 08:37 AM
Excel shoud not close all active books when clicking close button	technomike	Excel Discussion (Misc queries)	0	June 10th 05 05:35 PM
excel - Windows close button (x) should only close active workboo.	CoffeeAdict	Setting up and Configuration of Excel	3	February 8th 05 04:30 AM

Menu

About Us