Forum Discussion

ClinicalContributor's avatar
ClinicalContributor
Copper Contributor
Jan 16, 2025

If criteria is met in a certain column on the first sheet/tab of a workbook...

If criteria is met in a certain column on the first sheet/tab of a workbook, how can I have the entire row of info copied over onto a second sheet/tab?

 

I'm trying to create a finance tab on a procurement workbook - and if column N is "Full Receipt," i want the entire row to copy itself onto the finance tab

  • The action-led mindset behind this question makes it solution in Excel harder to envisage.  Manual processes can be triggered by conditions encountered on the first sheet of the workbook but Excel formulas (as functional programming) are set up to prevent such imperative action. 

    The alternative way of framing the question is to focus on the finance sheet and post the question 'what is it that I wish to see here?'  The FILTER function, placed on the finance sheet, examines data on the first sheet and returns values depending upon what it finds.  The formula would be of the form

    = FILTER(procurementTbl, procurementTbl[ColumnN] = "Full Receipt")

     

  • mathetes's avatar
    mathetes
    Silver Contributor

    I don't know the full picture of what your two (or more) sheets in that workbook are doing, but I will assume that the first one has a number of rows--perhaps hundreds--descriptive of actions in various stages;, and that the second in this case is intended to be a compilation of of all of the rows where column N reads "Full Receipt."

    \If that assumption is correct, another way to fulfill that intention would be to dynamically use the FILTER function so that it is always yielding a complete list of all on the first sheet where N reads "Full Receipt," not one by one filling them (via a macro or VBA routine perhaps). This way (using FILTER) that second sheet will remain accurate even if you discover you'd made an error and needed to change an entry in column N of the first sheet.

    Check out ;this video on the use of FILTER (and other Dynamic Array functions). See if it wouldn't serve your purpose.

Resources