So what I have here is two sheets in excel located in the same file. In column G when the field is changed to "Training", I need the Name, Phone #, and Email that are recorded on this page, to...

Question

Question

So what I have here is two sheets in excel located in the same file.

In column G when the field is changed to "Training", I need the Name, Phone #, and Email that are recorded on this page, to be automatically entered into column A, B, and C on the other excel sheet.

I am working with excel online, thank you for the help.

Phone # Hiring Process Name Email (412)777-4545 sampleemail@gmail.com Waiting on BG (412)777-4545 sampleemail@gmail.com (412)

012345678901 3-45-6789 01 222222 2-3-3 12345 6789

Phone # Hiring Process Name Email (412)777-4545 sampleemail@gmail.com Waiting on BG (412)777-4545 sampleemail@gmail.com (412)777-4545 sampleemail@gmail.com (412)777-4545 sampleemail@gmail.com (412)777-4545 sampleemail@gmail.com (412)777-4545sampleemail@gmail.com James Smith Training John Doe Training Chris Parker Jimmy Dean SHIELD Training Kelly Ott Training Mike Moore(412)777-4545 sampleemail@gmail.com 559 SHIELD (412)777-4545 sampleemail@gmail.com Waiting on DRG (412)777-4545 sampleemail@gmail.com (412)777-4545 sampleemail@gmail.com No Response (412)777-4545 sampleemail@gmail.com (412)777-4545 sampleemail@gmail.com (412)777-4545 sampleemail@gmail.com No Response (412)777-4545 sampleemail@gmail.com (412)777-4545 sampleemail@gmail.com No Response (412)777-4545 sampleemail@gmail.com (412)777-4545 sampleemail@gmail.com Waiting on DRG (412)777-4545 sampleemail@gmail.com (412)777-4545 sampleemail@gmail.com Paperwork (412)777-4545 sampleemail@gmail.com (412)777-4545 sampleemail@gmail.com (412)777-4545 sampleemail@gmail.com Jane Doe Paperwork Mike Trout 567 Sammy Sosa Training Barry Bonds Leaf Erikkson Paperworlk Kevin Knox Jordan Dunn Paperwork 580 Keith Henry 58 Kyle Right Paperwork 584 Austin Smith 585 lylke Chad King SHIELD Brad Drange Paperworlk 588 Steven Tyler Training Jose Valverde Paperwork
012345678901 3-45-6789 01 222222 2-3-3 12345 6789

business Operations-Management

Add a comment Improve this question Transcribed image text

Answer 1

Answer #1

To demonstrate the formulas, I have create two Excelsheets - "Data" and "Output" as shown below

Output sheet contains the blank table, in which formulas need to be entered.

Phone # Email Hiring Process Name James Smith(412)777-4545 sampleemail@gmail.com Waiting on BG John Doe (412)777-4545 samplee

Use the following Array formula, to list all match instances of "Training" in the table in new Excel sheet

To enter the formula, select blank cell A2 for placing the first matched instance, then enter formula =INDEX(Data!$D$2:$D$590,SMALL(IF("Training"=Data!$G$2:$G$590,ROW(Data!$G$2:$G$590)-ROW(Data!$G$2)+1),ROW(1:1))) into the Formula Bar, and then press Ctrl + Shift + Enter simultaneously.

A2 fx HINDEX(Datal$D$2:$D$590,SMALL(IF(Training-Datal$G$2:$G$590,ROW(Datal$G$2:$G$590)-ROW(DataI$G$2)+1),ROW(1:1)) B. 1 Nam

Keep selecting cell A2, and drag the Fill Handle down to get the other matched instances.

Select cell B2 and enter formula =VLOOKUP($A2,Data!$D$1:$F$23,2,0) (this is a normal formula and not array) and drag the Fill Handle down to copy the formula down

Select cell C2 and enter formula =VLOOKUP($A2,Data!$D$1:$F$23,3,0) (this is a normal formula and not array) and drag the Fill Handle down to copy the formula down

Add a comment

Answer 2

So what I have here is two sheets in excel located in the same file. In column G when the field is changed to "Training", I need the Name, Phone #, and Email that are recorded on this page, to...

Homework Answers

Add Answer to:
So what I have here is two sheets in excel located in the same file. In column G when the field is changed to "Training", I need the Name, Phone #, and Email that are recorded on this page, to...

Post as a guest

Earn Coins

So what I have here is two sheets in excel located in the same file. In column G when the field is changed to "Training", I need the Name, Phone #, and Email that are recorded on this page, to...

Homework Answers

Add Answer to: So what I have here is two sheets in excel located in the same file. In column G when the field is changed to "Training", I need the Name, Phone #, and Email that are recorded on this page, to...

Post as a guest

Earn Coins

Add Answer to:
So what I have here is two sheets in excel located in the same file. In column G when the field is changed to "Training", I need the Name, Phone #, and Email that are recorded on this page, to...