Software

Ask something
Question Locked

Excel Formatting Question

0 helpful
2 replies

by berlengg

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I would like to thank all of you in advance for your time and help with my question. I have started a workbook and have a product list/production sheet and an invoice. I have 127 products on my list, yet most customers only take a few at a time. I am relatively new to conditional formatting. Sheet "A" is my master production sheet, which I would like to use as my data entry point. Customers are in rows; products in columns. Sheet "B" is my invoice. Is is possible to put this into an equation? Only send to the invoice those products in row 2 with a value greater than 0? Would it be easier to put an equation into the invoice instead "only print this line(or lines) if the value in column "A" is greater than 0? The goal is to not list all 127 items on the invoice; saving ink and paper. One last thought; Would it be easier to formulate if the invoice fed the data to the production sheet, instead of the production sheet feeding the invoices, as I have it set up now? Once again, a great thank you for the help. I have learned a great deal from this site. You all are great teachers.

2 replies

by delbertpgh

This sort of thing should be addressed with a Visual Basic for Applications program (i.e., a macro.) However, if you're not ready to do that, you can do this on your invoice:

Turn on Data>Filter>AutoFilter. If your formula in your quantity section is showing a blank whenever there's an insufficient amount, you can opt to filter that column for nonblanks. That would cause every line with a blank quantity to be hidden.

You could also select a custom filter for the column, opting to only display a line if the quantity was greater than 2.

Since your invoice probably has lines above and below the item list area, you will need to first highlight the area you want to filter, then turn on the filtering. And there needs to be at least one completely blank line between the last item you want filtered and the footer area of the invoice, or it will go on filtering to the end of the page. And, on top of that, if you change data on the pick list, it won't appear or disappear from the filtered area on your invoice, unless you "select all" and reapply your filter.

Like I say, you should learn VB and do this right. There are so many "yes, but..." caveats about using the strategy here that it is sure not to win you fans among your clients.

This discussion is closed to new replies.