How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Zabo04 Your Own Question
Zabo04, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 283
Experience:  Experienced in Word, Excel, Access, Powerpoint, and Outlook.
Type Your Microsoft Office Question Here...
Zabo04 is online now
A new question is answered every 9 seconds

I'm trying to create a formula to copy and paste a row from

Customer Question

Hi, I'm trying to create a formula to copy and paste a row from one worksheet to a new worksheet using text entered in 3 different cells within that roll. If cell b2=PSC, cell c2=BIM and e2=active, I want the entire row copied from worksheet 1 and pasted in worksheet 2
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Zabo04 replied 1 year ago.

Are you concatenating those three cells into one, or keeping them split out in three cells? For example do you want the copy to be PSCBIMactive?

Expert:  Zabo04 replied 1 year ago.

If you can attach the spreadsheet, using the paperclip above the text box, with an example of what you want in the second sheet and the input from the first sheet that would be very helpful.

Customer: replied 1 year ago.
have attached the workbook
Expert:  Zabo04 replied 1 year ago.

So what is the source and where are the copies?

Expert:  Zabo04 replied 1 year ago.

I see the three spreadsheets, but I'm not sure what triggers the copies? Are you trying to lookup data when you enter the Employee Name all other columns get a values by referencing Site Services?

Customer: replied 1 year ago.
the source will be data entered on the first tab(site services) and depending on data entered in the 3 cells mention earlier will determine if the complete row will be copied to the second tab(breakdown by location active). if employees re inactive I like it copied to the third tab(breakdown by location inactive)
Expert:  Zabo04 replied 1 year ago.

To remove blanks it gets really complicated, because it needs to be an array formula. I am working on it.

Expert:  Zabo04 replied 1 year ago.

So I had to do a lot of stuff to get the information to copy. I changed the cell formatting, because formatting cannot be copied by formula. So I made it all conditional logic. If the cell is blank, no format, otherwise it formats with the fill you had in the grid.

The logic works by creating a formula array and multiplying the if logic statements, (IF(IF('SITE SERVICES'!B$2:B$63="PSC",TRUE,FALSE)*IF('SITE SERVICES'!C$2:C$63="BIM",TRUE,FALSE)*IF('SITE SERVICES'!E$2:E$63="active",TRUE,FALSE)=1,'SITE SERVICES'!A$2:A$63,"") So this logic checks for PSC and BIM and active. If everything matches it's true, or a 1, and the name in column A is used. Else false or 0, which is blank.

=IFERROR(INDEX(IF(IF('SITE SERVICES'!B$2:B$63="PSC",TRUE,FALSE)*IF('SITE SERVICES'!C$2:C$63="BIM",TRUE,FALSE)*IF('SITE SERVICES'!E$2:E$63="active",TRUE,FALSE)=1,'SITE SERVICES'!A$2:A$63,""),SMALL(IF(IF(IF('SITE SERVICES'!B$2:B$63="PSC",TRUE,FALSE)*IF('SITE SERVICES'!C$2:C$63="BIM",TRUE,FALSE)*IF('SITE SERVICES'!E$2:E$63="active",TRUE,FALSE)=1,'SITE SERVICES'!A$2:A$63,"")<>"",ROW('SITE SERVICES'!$AO$1:$AO$62),""),ROW(A1))),"") <- that whole thing, uses the matrix of names and blanks and removes the blanks. Which is used in cells A4 through A26 in Breakdown by location active. All other cells use a match =IF($A4="","",INDEX('SITE SERVICES'!$A$2:$E$63,MATCH($A4,'SITE SERVICES'!$A$2:$A$63,0),2)), so as you go across the cells the 2 value changes because that refers to the column it returns a value from when the match is found. The grid copies a . if the cell in the column for the employee is not blank and pushes the conditional logic. You do not see the . because the text is centered horizontally and vertically.

Lookups in tables and eliminating blanks is not trivial in Excel. So I did the mine site employees, per your example conditions. All you should have to do is change the logic two places in the if statements for the other positions, then copy and paste all the other formulas. I can do it, but I am not sure what logic fits where. Also, I wanted to check it this was correct before putting that much time in the wrong direction. Take a look at the attached workbook.

Related Microsoft Office Questions