# Copy Value of Cell from other Rows based on Multiple Criterias

#### pbsmith82

##### New Member
I am having trouble setting some automation. It works best if I explain I have three types of "Products" these products can be purchased Separately or as a collection. The can be "Completed" at different times. I collect the data as separate projects, but need to see them as a collection also ........ With that said Here is what I am trying to do.

If Collection is a Yes, I want Product A's , from Collection A, Active date Pasted to Cell X

AND

If Collection is a Yes, I want Product B's , from Collection A, Active date Pasted to Cell Y

AND

If Collection is a Yes, I want Product B's , from Collection A, Active date Pasted to Cell Z

See Image Below ....

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### jondavis1987

##### Active Member
Guessing from your description Column S is the Company, T is the product, U is Collection, V is collection code, W is active Date. =IF(AND(U2="Yes",T2="Software"),W2,"") This formula in Collection Software Active date will put all things that are yes in collection and software into the the Collection software Active date. Same idea but with hardware for this =IF(AND(U2="Yes",T2="Hardware"),W2,"") . This one will do Billing =IF(AND(U2="Yes",T2="Billing"),W2,"").

Now if you want to specifcy Company A, here are the three formulas for each in order of software, collection, and then billing

=IF(AND(U2="Yes",T2="Software",S2="Company A"),W2,"")

=IF(AND(U2="Yes",T2="Hardware",S2="Company A"),W2,"")

=IF(AND(U2="Yes",T2="Billing",S2="Company A"),W2,"")

#### Fluff

##### MrExcel MVP, Moderator
Cross posted https://www.excelguru.ca/forums/sho...l-from-other-Rows-based-on-Multiple-Criterias

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

#### pbsmith82

##### New Member
Thanks Fluff, Sorry About that and Jon .........

Guessing from your description Column S is the Company, T is the product, U is Collection, V is collection code, W is active Date. =IF(AND(U2="Yes",T2="Software"),W2,"") This formula in Collection Software Active date will put all things that are yes in collection and software into the the Collection software Active date. Same idea but with hardware for this =IF(AND(U2="Yes",T2="Hardware"),W2,"") . This one will do Billing =IF(AND(U2="Yes",T2="Billing"),W2,"").

Now if you want to specifcy Company A, here are the three formulas for each in order of software, collection, and then billing

=IF(AND(U2="Yes",T2="Software",S2="Company A"),W2,"")

=IF(AND(U2="Yes",T2="Hardware",S2="Company A"),W2,"")

=IF(AND(U2="Yes",T2="Billing",S2="Company A"),W2,"")
If I am understanding your Formula Correctly it will only evaluate the conditions of that row ....... I would need it to evaluate the conditions of other rows with the matching criteria ..... See Images Below .....

I've attached a Sample Workbook of it this as well ..... http://phillipbsmith.com/wp-content/uploads/2019/05/Excel-Guru.xlsx

#### jondavis1987

##### Active Member
I'm a little bit confused. Looking at your desired results in IHL it looks like you want every cell filled regardless of how many times IH & Lite is a product, Then in CRM you have the same date listed three times with spacing. Mil active date also has more cells filled then you have actual mil products and dates. What is with the seemingly random spacing and repeating of the dates?

#### Fluff

##### MrExcel MVP, Moderator
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### pbsmith82

##### New Member
I'm a little bit confused. Looking at your desired results in IHL it looks like you want every cell filled regardless of how many times IH & Lite is a product, Then in CRM you have the same date listed three times with spacing. Mil active date also has more cells filled then you have actual mil products and dates. What is with the seemingly random spacing and repeating of the dates?

Because Of Product & Project Information:
PRODUCT A: (Composed of three different Projects)​

• [*=1]MIL Site | ID: 10071E
[*=1]CRM | ID: 10071E
[*=1]IH & Lite CMB | ID: 10071E

PRODUCT B: (Composed of Two different Projects)

• MIL Site | ID: 10071E.1
• IH & Lite CMB | ID: 10071E.1

So I need the all the Associate dates for the other Associate Projects to be listed and if there isn't an associated project then it would be blank.

#### pbsmith82

##### New Member
I've tried these but no matter what I do It still returns the "" (error).

Excel 2013/2016
HIJ
231/10/201806/03/201911/03/2019
328/10/201813/03/2019
428/10/201813/03/2019
513/03/2019
613/03/2019
731/10/201806/03/201911/03/2019
831/10/201806/03/201911/03/2019

</tbody>
Sheet1

Array Formulas
CellFormula
H2{=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="MIL Site"),0)),"")}
I2{=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="CRM"),0)),"")}
J2{=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="IH & Lite CMB"),0)),"")}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

#### Fluff

##### MrExcel MVP, Moderator
Did you confirm the formualae with Ctrl Shift Enter?

#### pbsmith82

##### New Member
Never Mind I did Get it to work Thanks you!!!!!!

Excel 2013/2016
HIJ
231/10/201806/03/201911/03/2019
328/10/201813/03/2019
428/10/201813/03/2019
513/03/2019
613/03/2019
731/10/201806/03/201911/03/2019
831/10/201806/03/201911/03/2019

</tbody>
Sheet1

Array Formulas
CellFormula
H2{=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="MIL Site"),0)),"")}
I2{=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="CRM"),0)),"")}
J2{=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="IH & Lite CMB"),0)),"")}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

1,095,168
Messages
5,442,793
Members
405,197
Latest member
queryashish

### This Week's Hot Topics

• Copy entire row if CountA <>0 to another sheet
[B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
• Select last used Row in Table
I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
• excel workbook: do not allow certain file name
Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
• fixing problem autofilter
hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
• “Weight”
Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
• How to capitalize everything before a certain character?
In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...