Need Custom Function to Format Cell Values

[Alex20850]

I have values on a source sheet and a working sheet.

On the working sheet, I pull values from matching cells (Working!B6 gets the value from Source!B6 etc.) I need a custom function which looks at a cell such as Working!B1 to decide what to do in Working!B6:

If it is V or D, then add an apostrophe to be beginning and an apostrophe and comma at the end of Working!B6
If it is N, no apostrophe, but still a comma at the end.

In this case, I would use B$1 in the function, so I can copy it down and across with it adjusting for the different columns and rows.

 

[Trebor76]

Hi Alex20850,

This will put the apostrophe around the entry if the text is V or D or else it won't (hopefully this is what you need):

=IF(OR(Source!A2="V",Source!A2="D"),"'"&Source!A2&"',",Source!A2&",")

Robert

 

[Alex20850]

Thanks, that should do it.

 

[Trebor76]

That's great. Thanks for letting us know

 

[Rick Rothstein]

This will put the apostrophe around the entry if the text is V or D or else it won't (hopefully this is what you need):

=IF(OR(Source!A2="V",Source!A2="D"),"'"&Source!A2&"',",Source!A2&",")

It is not completely clear to me whether your sheet references should be to "Source" or "Working" although I kind of think it should be "Working". No matter which, I am pretty sure the cell addresses I highlighted in red should both be B6, not A2.

 

[Alex20850]

You are correct. The basic formula is enough for me to work with. Thanks