Aug 14 2021 07:46 AM
Aug 14 2021 07:46 AM
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
Aug 14 2021 10:06 AM
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.
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.
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.
Aug 14 2021 01:23 PM
Aug 14 2021 02:06 PM
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.
Aug 15 2021 10:51 AM
Aug 15 2021 02:37 PM
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.