Using a named range in an "IF" statement

%3CLINGO-SUB%20id%3D%22lingo-sub-1205282%22%20slang%3D%22en-US%22%3EUsing%20a%20named%20range%20in%20an%20%22IF%22%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1205282%22%20slang%3D%22en-US%22%3E%3CP%3EI%20saw%20the%20suggested%20IF%20statement%20at%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fif-statement-to-display-results-of-a-named-range%2Fm-p%2F100857%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fif-statement-to-display-results-of-a-named-range%2Fm-p%2F100857%3C%2FA%3E.%20I%20think%20I%20goofed%20up%20somewhere%20because%20it%20returned%20a%20VALUE%3F%20statement.%3C%2FP%3E%3CP%3EI'm%20trying%20to%20set%20up%20Excel%20to%20serve%20as%20the%20source%20for%20a%20Word%20mail%20merge.%20In%20column%20%22B%22%20are%20the%20letters%20A%20through%20F.%20In%20column%20%22M%22%20I%20have%20several%20named%20ranges%20-%20AFam%2C%20BFam%2C%20etc.%20These%20ranges%20refer%20to%20a%20two%20row%20one%20column%20phrase.%3C%2FP%3E%3CP%3EWhat%20I'm%20trying%20to%20achieve%20is%20when%20column%20%22B%22%20has%20an%20A%2C%20then%20the%20corresponding%20range%20will%20be%20inserted.%20Once%20the%20corresponding%20phrase%20is%20chosen%2C%20I%20want%20Word%20to%20complete%20the%20merge%20for%20a%20mailing%20we'll%20be%20doing.%20While%20the%20first%20go%20around%20has%20only%2036%20names%2C%20the%20next%20go%20around%20will%20have%20over%20250.%3C%2FP%3E%3CP%3EAny%20suggestions%20will%20be%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1205282%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1209393%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20a%20named%20range%20in%20an%20%22IF%22%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1209393%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F572680%22%20target%3D%22_blank%22%3E%40ParishAdmin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20couple%20of%20thoughts%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20it%20always%20helps%20the%20helpers%20on%20this%20forum%20if%20those%20of%20you%20with%20questions%20can%20post%20a%20copy%20(without%20personal%20or%20confidential%20info)%20of%20the%20spreadsheet%20in%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%2C%20in%20the%20absence%20of%20such%20a%20sample%20file%2C%20given%20the%20subject%20matter%2C%20I%20think%20you%20might%20be%20well%20served%20by%20looking%20to%20accomplish%20your%20%22If...Then...%22%20conditional%20in%20Word%20itself%2C%20rather%20than%20in%20Excel.%20You%20can%20search%20the%20help%20text%20for%20Word%20using%20a%20phrase%20like%20%22Conditional%20Mail%20Merge%22%20and%20get%20some%20step%20by%20step%20examples.%20%5BI%20did%20this%20recently%2C%20so%20know%20it%20works.%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20been%20literally%20a%20couple%20of%20decades%20since%20I%20did%20something%20comparable%20in%20my%20job%2C%20and%20that%20was%20for%20around%20a%20thousand%20employees.%20It's%20a%20challenge%2C%20but%20really%20fun%20when%20you%20make%20it%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECome%20back%20with%20a%20sample%20of%20your%20file(s)%20if%20you%20still%20need%20further%20help.%20But%20DO%20check%20out%20Word's%20abilities%20to%20do%20conditional%20merges%20in%20conjunction%20with%20Excel%20as%20the%20data%20source.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I saw the suggested IF statement at https://techcommunity.microsoft.com/t5/excel/if-statement-to-display-results-of-a-named-range/m-p/10.... I think I goofed up somewhere because it returned a VALUE? statement.

I'm trying to set up Excel to serve as the source for a Word mail merge. In column "B" are the letters A through F. In column "M" I have several named ranges - AFam, BFam, etc. These ranges refer to a two row one column phrase.

What I'm trying to achieve is when column "B" has an A, then the corresponding range will be inserted. Once the corresponding phrase is chosen, I want Word to complete the merge for a mailing we'll be doing. While the first go around has only 36 names, the next go around will have over 250.

Any suggestions will be greatly appreciated.

1 Reply
Highlighted

@ParishAdmin 

 

A couple of thoughts here.

 

First, it always helps the helpers on this forum if those of you with questions can post a copy (without personal or confidential info) of the spreadsheet in question.

 

Second, in the absence of such a sample file, given the subject matter, I think you might be well served by looking to accomplish your "If...Then..." conditional in Word itself, rather than in Excel. You can search the help text for Word using a phrase like "Conditional Mail Merge" and get some step by step examples. [I did this recently, so know it works.]

 

It's been literally a couple of decades since I did something comparable in my job, and that was for around a thousand employees. It's a challenge, but really fun when you make it work.

 

Come back with a sample of your file(s) if you still need further help. But DO check out Word's abilities to do conditional merges in conjunction with Excel as the data source.