Creating a variable print area, ignoring formulas.

supermanjohn01

New Member
Joined
Jul 10, 2018
Messages
26
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.
 
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.
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.
 
Upvote 0
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.
 
Upvote 0
Ok. Here are screen shots of what I did. Still can't get the code to stay.
 

Attachments

  • 1.jpg
    1.jpg
    224.7 KB · Views: 5
  • 2.jpg
    2.jpg
    233.9 KB · Views: 5
Upvote 0
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.

 
Upvote 0
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.

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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,992
Messages
6,175,822
Members
452,672
Latest member
missbanana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top