not printing cells based on data in another cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2648272%22%20slang%3D%22en-US%22%3Enot%20printing%20cells%20based%20on%20data%20in%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648272%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20a%20novice%20excel%20and%20am%20trying%20to%20not%20print%20the%20data%20in%20cells%20based%20on%20what%20is%20in%20another%20cell%20in%20the%20same%20row.%26nbsp%3B%20This%20is%20a%20name%20and%20address%20file%20but%20cannot%20show%20the%20phone%20number%20and%20or%20e-mail%20address%20if%20the%20resident%20has%20opted%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20file%20has%20a%20cell%20that%20has%203%20options%3B%3C%2FP%3E%3CP%3E1.%20if%20cell%20is%20blank(null)%20do%20not%20print%20the%20email%3C%2FP%3E%3CP%3E2.%20if%20cell%20says%20'ShowNameAndAddress'%20do%20not%20print%20phone%20number%20or%20email%3C%2FP%3E%3CP%3E3.%20if%20cell%26nbsp%3B%20says%20'ShowNameAndContactInformation'%20then%20print%20what%20is%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20doing%20with%20pivot%20table%20and%20power%20query%20but%20didn't%20see%20what%20I%20need%20to%20do.%20Can%20this%20be%20done%20in%20excel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2648272%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2648496%22%20slang%3D%22en-US%22%3ERe%3A%20not%20printing%20cells%20based%20on%20data%20in%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648496%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1128414%22%20target%3D%22_blank%22%3E%40fjpalinkas%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20a%20couple%20of%20ways%20to%20handle%20this.%20Before%20I%20get%20to%20that%2C%20I%20used%20the%20Special%20formatting%20options%20under%20Number%20formatting%20to%20display%20telephone%20numbers%20as%20we%20normally%20see%20them.%20You%20still%20just%20enter%20the%2010%20digits%2C%20and%20Excel%20takes%20care%20of%20formatting%20them.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1628960185036.png%22%20style%3D%22width%3A%20676px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303268i18B1F18C3EB069B4%2Fimage-dimensions%2F676x657%3Fv%3Dv2%22%20width%3D%22676%22%20height%3D%22657%22%20role%3D%22button%22%20title%3D%22mathetes_0-1628960185036.png%22%20alt%3D%22mathetes_0-1628960185036.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20to%20your%20actual%20question%2C%20probably%20the%20most%20effective%20%3CU%3E%3CEM%3Elong%20term%3C%2FEM%3E%20%3C%2FU%3Esolution%20would%20be%20to%20learn%20to%20use%20the%20Excel%20database%20as%20a%20source%20for%20a%20MailMerge%20in%20Word.%20You%20can%20print%20the%20fields%20you%20want%20based%20on%20the%20cell%20containing%20the%20criterion%20for%20each%20group.%20If%20this%20is%20going%20to%20be%20a%20long%20term%20project%2C%20with%20varying%20conditions%20each%20time%2C%20new%20members%2C%20members%20changing%20their%20mind%2C%20etc.%2C%20that%20would%20be%20my%20recommendation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CEM%3EEasier%20would%20be%20to%20learn%20to%20use%20the%20Filter%20capability%20represented%20by%20those%20little%20funnel%20symbols%3C%2FEM%3E%3C%2FU%3E%20at%20the%20top%20of%20each%20column.%20You%20could%20filter%20the%20database%20based%20on%20%22OptinSelection%22%20choices%2C%20and%20then%20choose%20columns%20to%20print.%20That%20would%20get%20burdensome%20if%20you're%20doing%20all%20the%20changing%20I%20allude%20to%20above%2C%20but%20if%20this%20is%20a%20one-off%20proposition%2C%20easily%20done.%20I've%20set%20it%20coming%20in%20the%20attached%20return%20file%20so%20only%20the%20blank%20rows%20are%20showing.%20And%20you%20can%20just%20set%20the%20print%20area%20to%20print%20names.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_1-1628960488106.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303270iE78FDA00B35364F8%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_1-1628960488106.png%22%20alt%3D%22mathetes_1-1628960488106.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPivot%20Table%20would%20not%20be%20relevant%20at%20all.%20Power%20Query%20probably%20not%20all%20that%20useful.%20You're%20trying%20to%20use%20a%20sledge%20hammer%20when%20far%20more%20nuanced%20tools%20will%20do%20what%20you%20need%20to%20do.%20Not%20even%20sure%20why%20you've%20got%20a%20macro%20in%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, I am a novice excel and am trying to not print the data in cells based on what is in another cell in the same row.  This is a name and address file but cannot show the phone number and or e-mail address if the resident has opted out.

 

The attached file has a cell that has 3 options;

1. if cell is blank(null) do not print the email

2. if cell says 'ShowNameAndAddress' do not print phone number or email

3. if cell  says 'ShowNameAndContactInformation' then print what is there.

 

I tried doing with pivot table and power query but didn't see what I need to do. Can this be done in excel

 

 

5 Replies

@fjpalinkas  

 

There are a couple of ways to handle this. Before I get to that, I used the Special formatting options under Number formatting to display telephone numbers as we normally see them. You still just enter the 10 digits, and Excel takes care of formatting them.

mathetes_0-1628960185036.png

 

Now, to your actual question, probably the most effective long term solution would be to learn to use the Excel database as a source for a MailMerge in Word. You can print the fields you want based on the cell containing the criterion for each group. If this is going to be a long term project, with varying conditions each time, new members, members changing their mind, etc., that would be my recommendation.

 

Easier would be to learn to use the Filter capability represented by those little funnel symbols at the top of each column. You could filter the database based on "OptinSelection" choices, and then choose columns to print. That would get burdensome if you're doing all the changing I allude to above, but if this is a one-off proposition, easily done. I've set it coming in the attached return file so only the blank rows are showing. And you can just set the print area to print names.

mathetes_1-1628960488106.png

 

Pivot Table would not be relevant at all. Power Query probably not all that useful. You're trying to use a sledge hammer when far more nuanced tools will do what you need to do. Not even sure why you've got a macro in here.

 

Thanks for you reply and suggestions. I thought maybe I could use a macro but couldn't see how that would work.
Think I will try the mail merge, This a N&A list for our community and will want to run a new list every quarter or so.
Never used mail merge before wish me luck.

@fjpalinkas 

 

Mail Merge, in Word, with an Excel database (which is what you have) as the source, can be a very powerful and useful tool.

 

To make the IF-THEN aspect of this easier for you, I've created some new columns that take the conditions you specified and fill in HomePhone4Print, CellPhone4Print, etc., using the conditions you've described. So you can just use Mail Merge to print those fields, and rely on the formulas in Excel to only show data that people are willing to share.

Note, I added a column with what's called Data Validation, to enter the three conditions in just a single shorter word. This made it possible to have the IF function in my new columns less "wordy."

 

I assume, from the fact that you refer to addresses, that your real database has mailing addresses as well as all of these bits of info......I hope you can follow the logic in the formulas that are here to incorporate those additional elements.

Thank you for the additional info and formulas in the excel. Question I have is about the extra field for the 3 types of conditions, is there a formula to create this or did you key it manually?
I understand the 4print fields formulas, I worked in IT for over 45 years, but on mainframes 360/20-30, then system 38, as400, ISeries. Did little PC database programming, mostly RPG and DB2 using DBU and DFU and IBM Query. Just never had time to become a expert in Excel, Access or Word.
Will work on your tips and formulas and try and create the mail merge, will let you know how I make out.
Thanks Again
Frank

@fjpalinkas 

 

That cell with the three conditions uses Data Validation. There are several ways to create the criteria for what's "valid" and several ways to use it. HEre's a great source for instruction on what it is and how to use it. https://exceljet.net/excel-data-validation-guide

 

Feel free to come back with questions on that or any other item.