Creating a variable print area, ignoring formulas.

[supermanjohn01]

As simple as I can put it, I have created an excel worksheet that is an invoice. Using formulas, the invoice, (we'll call worksheet A), pulls information from another work sheet, (worksheet B), but if the information is blank on worksheet B, then it is also SHOWING blank on worksheet A, although the formula is there, (=IF(worksheet B="",""). I only want the rows printed on the invoice that are no longer showing blank cells, drawn from worksheet B. The current information I find online is still trying to print the cells that have a formula, but are not showing blank. I need DETAILED help. Assume I know nothing. The name of my invoice is Contract Invoice. The area parameters are A1:M76. Nothing I have tried has worked. I don't know if this is clear or not, but I need help from someone that REALLY knows what they are doing. Thanks in advance.

 

[supermanjohn01]

First off, the data on the 'Contract Invoice' sheet is drawn from the 'Contract Quoter' sheet, and on the 'Contact Quoter' sheet the columns A, C-H, J (dropdown menu), K, L, M & N (dropdown menus), and R are all data input cells. All other cells are either hidden or have formulas that provide data based on the input cells. The "Contract Invoice" sheet should pull ALL the information from the "Contract Quoter" sheet. Neither of these sheets should be protected, so you should have free access to play with it and see how it works. As the data adds to the "Contract Invoice" sheet, if functioning properly, it should print down through the information that is seen. Every other thing I've tried, either doesn't work right, or it tries to continue to print the cells that are blank, but do have formulas in them.

Here is what I have done so far with what you gave me, but it didn't work, so tell me what I have done wrong, or what answers you need from me to insure that it works the way I want. And by the way, thank you for your patience!!! I set a standard Print area, A1-L14, then went to "Name Manager" and found the created print area, and replaced the Reference window with your formula. For some reason, your formula will not stay saved in the Reference window. Not sure what's causing that, because it wasn't doing that yesterday.

 

[6StringJazzer]

First off, the data on the 'Contract Invoice' sheet is drawn from the 'Contract Quoter' sheet

The Contract Quoter sheet is set to Very Hidden and the VBA project is password protected so I cannot access it to make the sheet visible.

 

[6StringJazzer]

Here is what I have done so far with what you gave me, but it didn't work, so tell me what I have done wrong, or what answers you need from me to insure that it works the way I want. And by the way, thank you for your patience!!! I set a standard Print area, A1-L14, then went to "Name Manager" and found the created print area, and replaced the Reference window with your formula. For some reason, your formula will not stay saved in the Reference window. Not sure what's causing that, because it wasn't doing that yesterday.

There doesn't seem to be anything attached to the post to show what you have done so far.

I had the same problem with the formula, and it turned out that the reason was that there was no data on the invoice page, so the formula returned an error. You can't enter a new formula that returns an error. There has to be at least one row of data in the table to be able to enter the formula. I think I mentioned that a couple of posts back.

Glad to help, I find it kind of an interesting problem that other people might also find useful.

 

[supermanjohn01]

Don't know what to do. I put data in there, but it still won't save the code.

 

[supermanjohn01]

Ok. Here are screen shots of what I did. Still can't get the code to stay.

 

[supermanjohn01]

Here are the 3rd and 4th screen shots.

 

[6StringJazzer]

Here is your file after I updated it with that formula. However, I had to type in the data because the Contract Quoter sheet is set to Very Hidden and the VBA project is password protected so I cannot access it to enter any data.

Coastal Trip Calculator.xlsm

Shared with Dropbox

www.dropbox.com

 

[supermanjohn01]

Here is your file after I updated it with that formula. However, I had to type in the data because the Contract Quoter sheet is set to Very Hidden and the VBA project is password protected so I cannot access it to enter any data.

Coastal Trip Calculator.xlsm

Shared with Dropbox

www.dropbox.com

Well, I downloaded the updated file, but it's still trying to print all three pages, with the last two being blank sheets of paper. I must be doing something wrong, so I'm giving up I guess, and I'll just tell my boss, on the print page, to just choose how many pages he wants printed to avoid two blank sheets of paper each time. But, THANK YOU FOR YOUR EFFORTS. I truly appreciate it.

 

[6StringJazzer]

Your first screenshot shows that the first row of data is in row 11, and that is what my formula is based on. But the actual file you provided puts it in row 14.

I updated my file to fix that. If you download the file again from the same link you will get the update. This works for me, it does not print all three pages.

However, your print format is too wide and doesn't fit on one page. So when there is one page of actual data, it prints two pages.

 

[supermanjohn01]

I opened up that last file you sent and filled it with information to test it, So I filled with information down to row 32, and when I did, it only shows that it is going to print through column H, and is still only printing one page, down to row 14. Again, thank you for trying. I know this is very challenging, but for some reason, I can't get it to work. My boss is just going to have to live with it the way it is.