Forum Discussion

null null's avatar
null null
Copper Contributor
Jan 25, 2018

Need help with if function or macro copy cells if cell = y?

I have a workbook with three sheets titled Master (Sheet 1), Deceased (Sheet 2), Veteran (Sheet3). I need a formula that COPIES a row from the Master to either the Deceased or Veteran tab depending on a Y entry in a cell. I want this to work for the whole workbook. So I want the row to be retained on the master, but then move to Deceased if there is a Y entered in column A. I want the row to be copied from the master to Veteran if Y is entered in column AM. If possible I would like to keep all formatting the same when it copies ie: I have conditional formatting set up on certain columns just to populate a color if the value is something I've chosen. I realize I could just sort the master and copy things, but I'd really like if it would just HAPPEN! I'm not an excel wiz so an explaination of how to insert the function would be helpful.

 

THANK YOU

Formulas & Functions
Show and Tell
  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    Jan 25, 2018

    Hello,

     

    if you keep all the data in the Master sheet, why do you want to duplicate the data into other sheets? Duplicating data is not good data architecture, because then the data can be changed in two places and how do you ensure that master and copy are kept in sync? How do you know which version is the correct one?

     

    So, from a data architecture point, keep only one master list, and then filter or report to see only the data you're interested in.

     

    You could turn on Autofilter on the Master and quickly filter to see only the rows where Veteran is "Y", or Deceased is "Y" or any other filter. 

     

    Or, you could use a report sheet, build a pivot table and pull the data from the Master sheet. The pivot table can have filters to show only Veterans or other filters. The point here is that there should be only one place where the data is stored and edited.

     

     

    • null null's avatar
      null null
      Copper Contributor
      Jan 25, 2018

      That does make sense, I wanted to have it sorted for me. In this case, once the data is entered it really won't change. I am talking about logging a cemetery list of interments. Those are fairly final. I do however understand what you mean and it is a very good point. I can play with a pivot table. I have a very limited knowledge of excel so I'll cross my fingers.

       

      Thank you

Resources