Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7

Maybe a different approach now you have more variables

=LOOKUP(B7,{"H","K","M","P"},{"JER","SHJ","MCJ","M OP"})

Mike

"Workbook" wrote:

Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7

Workbook wrote:
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7



One way:

=IF(ISERROR(FIND(B7,"HKMP")),"",MID("JERSHJMCJNOP" ,(FIND(B7,"HKMP")-1)*3+1,3))

Very nice

"Glenn" wrote:

Workbook wrote:
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7



One way:

=IF(ISERROR(FIND(B7,"HKMP")),"",MID("JERSHJMCJNOP" ,(FIND(B7,"HKMP")-1)*3+1,3))

Hi,

You may also use the VLOOKUP() function. Please try
=VLOOKUP(B7,$C$9:$D$12,2,0). C$9:$D$12 is a table with two columns where
column C has H,K,M and P. Col. D has the acronyms

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Workbook" wrote in message
...
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that
if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7

This is great. It works awesome. Thanks Mike.

I am wondering, Is it possible to adjust this part of the statement
,{"H","K","M","P"} further to accomodate 10 different words of varying
lengths?

For instance, when I've tried something like this ,{"Wireless
Laptop","Desktop Computer","Flatscreen Monitor","Server","Solar
Panels","I-Phone","I-Pod","Dual Screen Monitor Stand","Wirless
Router"}{"JERA","SHJK","MCJM","MOPQ","ABPE","POMZ" ,"PDAI","COPN","ZTYL","HLMW"})
, some of them work and some of them don't and I couldn't figure out why.
Any thoughts?

"Mike H" wrote:

Maybe a different approach now you have more variables

=LOOKUP(B7,{"H","K","M","P"},{"JER","SHJ","MCJ","M OP"})

Mike

"Workbook" wrote:

Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7

Thanks Glen, this is really cool. It works great.

Is this part of the code ")-1)*3+1,3)) saying something to the effect of
(excuse my VBA translation), count 3 letters there and stick 3 letters here,
count 1 letter here and stick one letter there? Does my question make sense?




"Glenn" wrote:

Workbook wrote:
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7



One way:

=IF(ISERROR(FIND(B7,"HKMP")),"",MID("JERSHJMCJNOP" ,(FIND(B7,"HKMP")-1)*3+1,3))

Agreed!

"Mike H" wrote:

Very nice

"Glenn" wrote:

Workbook wrote:
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7



One way:

=IF(ISERROR(FIND(B7,"HKMP")),"",MID("JERSHJMCJNOP" ,(FIND(B7,"HKMP")-1)*3+1,3))

Thank you for you're input. I am not sure if I implemented this formula
correctly. I had some trouble making it work. I wonder if it's something I
did wrong.

"Ashish Mathur" wrote:

Hi,

You may also use the VLOOKUP() function. Please try
=VLOOKUP(B7,$C$9:$D$12,2,0). C$9:$D$12 is a table with two columns where
column C has H,K,M and P. Col. D has the acronyms

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Workbook" wrote in message
...
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that
if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7

The statement (FIND(B7,"HKMP")-1) will return a number from 0 to 3 depending
upon the contents of B7. That result times 3 plus 1 (*3+1) turns {0, 1, 2, 3}
into {1, 4, 7, 10}. So, assuming B7 contains "M", you would get 7. The rest is
straight forward:

MID("JERSHJMCJNOP",7,3)="MCJ"


Workbook wrote:
Thanks Glen, this is really cool. It works great.

Is this part of the code ")-1)*3+1,3)) saying something to the effect of
(excuse my VBA translation), count 3 letters there and stick 3 letters here,
count 1 letter here and stick one letter there? Does my question make sense?




"Glenn" wrote:

Workbook wrote:
Thank you TValko for this statement =IF(Sheet1!B7="H","JER",""). I am
wondering can it be modified to include the 4 following variations so that if
H, K, M, or P is entered into cell B7 the associated acronym will appear?

If K is entered in cell B7 on Sheet1 then SHJ will appear in J7
If M is entered in cell B7 on Sheet1 then MCJ will appear in J7
If P is entered in cell B7 on Sheet1 then NOP will appear in J7


One way:

=IF(ISERROR(FIND(B7,"HKMP")),"",MID("JERSHJMCJNOP" ,(FIND(B7,"HKMP")-1)*3+1,3))