How can I create a summary list of items and quantities based on whether or not there is a value (quantity) in a cell? If I have a column A with a Master List of 500 items and there is a neighboring column B for typing a value or Quantity for selected text items, how can a list be automatically generated on it's own worksheet page? This assumes for example, that of the 500 items on the master list, only 20 may be selected with varied quantities in the neighboring cell. Identifying the selected items depends on observing if the Quantity Column has a value in the cell next to the item. The point would be to create a concise, printable list, including quantities, that ignores the other 480 items that have no quantities in the neighboring cells.

  • 1
    Why not just turn on the data filter and select the filter if not equal to 0? – EkriirkE May 20 '14 at 23:06
  • That makes sense, however I am unfamiliar with that function. My question would be, if I need to type that formula into a cell on another worksheet, would I have to do this for 500 cells or will a data filter apply to an entire page or range of cells. ( I am self taught and new to this). Thank you – Warren May 20 '14 at 23:11
  • You dont need any formulas. Filters are very simple, as long as your "master list" has a regular shape i.e. consistent rows and columns. Just select any single cell in the range, go to the Data ribbon and choose Filter. Then use the filter "down arrow" button added to each column heading to select or deselect the values you want. – Mike Honey May 20 '14 at 23:35

If you just highlight the worksheet (easiest by clicking the top-leftmost blank corner for the row and column headers) and on the data toolbar click the funnel(filter) icon you get dropdowns for each column. This allows you to display only rows where that column matches the selected filter. enter image description here

In the dropdown for column B there is a "Numbers" option the "Does not equal..." where you will see a dialog to set the "Does not equal 0"

enter image description here

See http://www.wikihow.com/Use-AutoFilter-in-MS-Excel

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.