I would like to click once on a cell

Attach a sample workbook.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

Any thoughts?

Just autofilter to hide rows with blanks or cells where the quantity is >= 1.
Excel only prints visible rows.

if you look on the home tab, on the far right, look for the "sort and filter" icon. When you click on that, each heading will have a small drop-down arrow. When you click on that, you will see all the unique entries in that column - uncheck the 1's you dont want to see, and those rows will be hidden.

If you have a column with QTY - ie with a value, you can filter on that using > or <


Edit: also, please do not post duplicate threads, even if you use a different name!!

Excel only allows entire rows or columns to be hidden - not single cells.

If you need specific cells only to be printed then the solution is to have them copied to another sheet that contains only the data you want.
Depending on your worksheet this could be accomplished using formulas but it may require VBA coding. Of course, simple cell linking is always an option (i.e. on the "report sheet" you would link, say, sheet2!A1 to sheet1!C5 with this formula: =sheet1!C5

See these links for tutorials on using autofilter

Excel Filters: Excel 2007 AutoFilter Basics

YouTube Video Tutorial

I just saw your other thread (now closed) and have a better understanding of your needs.

My recommendation is to use Data Validation with dependent lists.
See the attached workbook for a basic example.

On the Shopping List sheet, columns A and B contain drop down lists using Data Validation. The "Item" list is dependent on the selection made in the "Category" list.

Cells in column-A use a list named "Category", which is created using a dynamic named range to refer to row-1 on the DV Lists sheet.

This formula is used to create the Category list
='DV Lists'!$A$1:INDEX('DV Lists'!$1:$1,COUNTA('DV Lists'!$1:$1))

Cells in column-B use Data Validation, List option and this formula to dynamically create the item list for the chosen category.

=OFFSET('DV Lists'!$A$1,1,MATCH(A4,Category,0)-1,COUNTA(INDIRECT(A4)),1)

Named ranges were created for all of the category lists using formulas like:


Bread ='DV Lists'!$C$2:$C$100
Dairy ='DV Lists'!$D$2:$D$100
Fruit ='DV Lists'!$E$2:$E$100
Pasta ='DV Lists'!$B$2:$B$100
Produce ='DV Lists'!$A$2:$A$100


When you are ready to create a shopping list, just select a category then an item.
Clear previous lists by selecting all non-empty cells and press the delete key.

Excel will only print visible cells and there is no need for filtering.

Topics you should explore: named ranges, Data Validation, Dependent Lists
A general web search on the above terms will yied plenty of help.


Shopping List Help.xlsx

I have a sheet that i made up for my wife to do her ordering of supplies at work. I think it will work for you, either as is or with minor modifications. Just fill in what items you normalyl list for shopping purposes, and make note of maximum quantity wanted at any time & minimum quantities before ordering. Then all you need to do is adjust your stock count before you go shopping and hit print. Only the right hand section prints and all that shows is the items you currently need.