If Then Replace

[mikeyates]

I want to reference another cell, and if it has a certain value (only 5 options), then I want to output something else in it's stead.

So, for example, if 'Sheet2!A1=ABC, then instead of putting ABC, I want it to say XYZ

I created another sheet with the assigned co-values, if that helps. I thought I might could just point the reference to another reference, but I can't get the if then to work.

Is there another formula?

What am I missing?

Thanks in advance.

 

[duggie33]

Hard to say for sure without more detail. Look at the IFS function.

Hope that helps,

Doug

 

[awoohaw]

Asking for additional clarification. Are you saying that if a cell contains the text "ABC" (not a formula) to overwrite it with "XYZ".
If so, that cannot be done with a formula. But it can be done with VBA.

 

[mikeyates]

I found it. I wasn't using the IFs correctly. Thanks for the quick response. You guys are awesome.

 

[mikeyates]

This was the final syntax that wound up solving everything, incase you were curious.

="<class catalognum="&CHAR(34)&IF('1. Registration'!A1="CCD",'14. Base Info'!B1,IF('1. Registration'!A1="MPDR",'14. Base Info'!B2,IF('1. Registration'!A1="CPD",'14. Base Info'!B3,IF('1. Registration'!A1="CFR",'14. Base Info'!B4,IF('1. Registration'!A1="NTA",'14. Base Info'!B5,IF('1. Registration'!A1="CYBER",'14. Base Info'!B6))))))&CHAR(34)

 

[duggie33]

Thanks, glad you got it working. The IFS functions might also work...

IFS function - Microsoft Support

The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. IFS will allow you to include up to 127 pairs of conditions (the test if something is True and the result if something is true). IFS can take the place of multiple...

support.microsoft.com

Doug

 

[mikeyates]

Thanks, glad you got it working. The IFS functions might also work...

IFS function - Microsoft Support

The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. IFS will allow you to include up to 127 pairs of conditions (the test if something is True and the result if something is true). IFS can take the place of multiple...

support.microsoft.com

Doug

Oh. I didn't read your reply as IFS. I read it as IFs, as in multiple IF statements. I've never heard of IFS, but now I'm curious. Thanks for the tip. I'm still new at learning these things.

 

[mikeyates]

Thanks, glad you got it working. The IFS functions might also work...

IFS function - Microsoft Support

The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. IFS will allow you to include up to 127 pairs of conditions (the test if something is True and the result if something is true). IFS can take the place of multiple...

support.microsoft.com

Doug

I had to do it again with different data, and the IFS worked a lot easier!

=IFS('8. Data Entry'!B6="Mike",'14. Base Info'!B8,'8. Data Entry'!B6="Sal",'14. Base Info'!B9,'8. Data Entry'!B6="Steve",'14. Base Info'!B10)