Forum Discussion
HELP!! Formulas & Printing!!
I have a spreadsheet that i use for ordering materials. It has 3 columns & over 1000 rows. When i print the document it is several pages long and not really "easy" to see the items needed.
I am trying to make it so that if under the quantity (QTY) column if there is a value of 1 or more that the 6 cells containing important info (REM#, Size, Description, Amount, Quantity and Total) are "COPIED" to a print Page, so that the only thing that prints is items to be ordered. See following picture
PLEASE HELP!!
8 Replies
- Use FILTER().
- could you more specific?
- I let Copilot answer that.
The FILTER() function in Excel is a powerful tool that allows you to filter a range of data based on criteria you define. Here’s a more detailed explanation:
Syntax: FILTER(array, include, [if_empty])
Arguments:
array: The array or range to filter.
include: A Boolean array whose height or width is the same as the array.
if_empty (Optional): The value to return if all values in the included array are empty (filter returns nothing).
Here are some examples of how you can use the FILTER() function:
Returning multiple criteria: You can use the multiplication operator (*) to return all values in your array range that meet multiple conditions. For example, =FILTER(A5:D20, (C5:C20=H1)*(A5:A20=H2),"") will return all values in the range A5:D20 that have the value in cell H1 in column C and the value in cell H2 in column A.
Returning multiple criteria and sorting: You can combine the FILTER() function with the SORT() function to return and sort values that meet multiple conditions. For example, =SORT(FILTER(A5:D20, (C5:C20=H1)*(A5:A20=H2),""),4,-1) will return all values in the range A5:D20 that meet the conditions specified, and then sort the fourth column (column D) in descending order.
Remember, none of the functions require absolute references, since they only exist in one cell, and spill their results to neighboring cells. This makes the FILTER() function a flexible and dynamic tool for managing and analyzing data in Excel.
