Hiding cells with if statements

[mik9517]

Hi everyone I'm new to the forum but having an issue that I was wondering if you guys could help me with.

I'm working on a spreadsheet as a favour for a friend at her work. The spreadsheet is a large form she uses to collect customer data on the phone and she has asked me to put this data into another tab in the file which builds a letter that then gets printed out and sent to the customer.

The problem is there is a large section of this letter which only gets added if the customer says they have a specific requirement which on the form sheet is a Yes or No answer (as it concerns legal stuff they have to have if this requirement is true). I have an if formula set up with data validation where if the cell equals Yes to show the text but if it equals No to just be empty "" i.e. =IF(A1="Yes","Display text","").

However when you go to print the letter out the normal hide empty cells doesn't work as obviously the formula is still there so it's technically not empty and instead there are massive white gaps.

I do know you can manually hide the row before you print it but that would be a pain if my friend were doing it 8-10 times a day so I just wondered if there was a setting which can be used to do this each time?

Many thanks in advance!

[6StringJazzer]

Are you saying that there is a sheet with a form letter, and if one of the fields is blank it shows a big blank gap instead of compression out the space? You can't hide a single cell, only an entire column or entire row.

Very difficult to imagine without an example. Can you attach a file with fake data?

Also, maybe Word Mail Merge is what you need to use instead of doing it all in Excel.

[mik9517]

Hi, sorry for my late response.

For an example I have this cell in the letter sheet (A41) where you can see a large paragraph but the paragraph only needs to appear in the letter if on another tab of the spreadsheet someone says it is for Debt Consolidation (the spreadsheet is for a mortgage application form so it unfortunately wouldn't be doable with a mail merge).

Attachment 846813

However if on the spreadsheet someone selects a different loan purpose then this section doesn't need to appear, I've set up the formulas with the if statement above so visually on the sheet it doesn't show the paragraphs but if I go to print it then I end up with big white gaps like in the screenshots below.

Attachment 846814
Attachment 846814Attachment 846815

What I need is a way to hide this so that when it prints it reads ok - I can hide the individual rows due to how the spreadsheet is setup but wondered if there is an easier way to do this as my friend would then need to do this every time (there are a few other instances in the letter where other sections have to be added/taken out too).

Hopefully this clarifies everything and thank you in advance!

[Glenn Kennedy]

We can't do anything with pictures... Please attach an excel file.

[Pete_UK]

You could use a column outside your printed area with a formula that returns, say, yes or no if that row is to be printed (depending on the contents on that row). Then before printing you can just apply a filter to this helper column and de-select no.

Hope this helps.

Pete

[mik9517]

Thank you Pete that worked brilliantly! I was looking on Sunday morning at the filters but couldn't quite work out a way to do it but that works just how I need it to

[Pete_UK]

Glad to help. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete