New Contributor

I am creating a workbook that manages paint inventory where I track dates and have a reordering level column. I am stuck trying to figure out how to generate a list that gets created in another sheet within the workbook that gives me the following information:


copies information of a row when the conditional formatting of the expiration date is met into a new sheet.


copies information of a row when the stock levels fall below the reordering level set into a new sheet. 



I've never created a VBA.




3 Replies



I have two recommendations for you:

A) Concentrate more on creating a functional basic data table and less on making it look beautiful (with colors and formats). Conditional formatting is fine, but good spreadsheet design textbooks all recommend against such things as big title rows that combine columns, and excessive use of colors for display purposes only.


B) Look into the FILTER function. Unless I'm mistaken, that will enable you to do both of the things you're seeking to do,

  1. extract the rows based on the same condition that triggers conditional formatting (i.e., not on the resulting format, but on the underlying condition)
  2. extract rows when stock levels fall below specified levels

Here are two good references for FILTER


Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...
The color is not for display purposes only. This Excel will be used as inventory control and the color matches the label that will be placed on inventory received. Many will have access to the data collected in this spreadsheet. I am wanting to eliminate the purpose of using the filter function and just have the information pulled automatically into another spreadsheet.


I apologize: my critique was referring primarily to the heading on your sheet (as shown in your image), not to the other colors, which I acknowledge could be functional.


Further, thought, the FILTER function I referred to DOES pull information into another sheet. It's quite different from the Filter capability applied to a table using the little caret at the top of a column. I trust you've taken a look at the links I provided.