Formula help

[abignale]

Hello. I am building a call center agent score card.

on sheet 1 I will have my report data pasted in.

On sheet 2 I will have a score card template in place.

In the first box I will have a data validation box set up with a list drop down of all of the agents.

In all of the other boxes on the score card I would like to have a formula read as follows:

If Box A1 (The box with the name drop downs) = bob smith (the first name on the sheet 1) then all of the other boxes will equal different values of the same row.

So - on sheet 2: If A1 = (Sheet1 B3) then (sheet 2) C1 should = (Sheet 1 B4)

 

[Roderick_E]

Hi there.
In each of the score card cells you will need to use index-match, and point it to the value you want returned from the data list.

So, imagine on Sheet1 you have a column with the Agent name (like it will appear on the dropdown), and then columns with different values on same row.
On sheet2 in each of the cells for the scorecard you would have this formula:

=index(sheet1!B:B,match($a$1,sheet1!A:A,0))

This assumes the agents names are listed in column A of sheet1. Adjust as needed. This returns the data from column B of the matched row. Also adjust as neede for each cell in the scorecard.
Perhaps a better solution would be to simply make a serious of pivots controlled by slicers.

 

[abignale]

Thank you for the response. That formula does not work. Or (probably more accurately) I could not get it to work. I have included some visuals to better illustrate my needs.

Sheet 1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Agent[/TD]
[TD]AHT[/TD]
[TD]ACW[/TD]
[TD]QA SCORE[/TD]
[/TR]
[TR]
[TD]Bob Smith[/TD]
[TD]3:00[/TD]
[TD]00:30[/TD]
[TD]95%
[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]3:15[/TD]
[TD]00:20[/TD]
[TD]96%[/TD]
[/TR]
[TR]
[TD]Tom Jones[/TD]
[TD]3:28[/TD]
[TD]00:35[/TD]
[TD]92%[/TD]
[/TR]
[TR]
[TD]Mark Paul[/TD]
[TD]2:58[/TD]
[TD]00:28[/TD]
[TD]96%[/TD]
[/TR]
[TR]
[TD]Sam Smith[/TD]
[TD]3:15[/TD]
[TD]00:23[/TD]
[TD]98%[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Agent Name[/TD]
[TD]Bob Smith[/TD]
[/TR]
[TR]
[TD]KPI[/TD]
[TD]Current Score[/TD]
[/TR]
[TR]
[TD]AHT[/TD]
[TD]3:00[/TD]
[/TR]
[TR]
[TD]ACW[/TD]
[TD]00:30[/TD]
[/TR]
[TR]
[TD]QA SCORE[/TD]
[TD]95%[/TD]
[/TR]
</tbody>[/TABLE]

So on sheet 2 where it says "Bob Smith" this will be my data validation drop down box (populated from the names on sheet 1). When I select Bob Smith from the drop down list I would like the boxes below to auto populate with the data from Sheet 1 for Bob Smith. If I select Jane Doe from the same drop down then all the info would change to Jane Doe's info from sheet 1. I have tried a few if formulas but nothing works. When I tried the formula you suggested it just defaults to NA.

 

[RasGhul]

Names a Data Validated in Sheet 2 B1,


Try in the "AHT" row;

=INDEX(Sheet1!B$2:B$6,MATCH($B$1,Sheet1!$A$2:$A$6,0))

in the "ACW" row and drag down to "QA Score";

=OFFSET(INDEX(Sheet1!B$2:B$6,MATCH($B$1,Sheet1!$A$2:$A$6,0)),,ROWS($B$4:B4))

 

[abignale]

Hello. This did not work either. I just keep getting #N/A.

 

[Joyner]

Hello Try on Sheet 2, cell B3,4,5:

=INDEX(Sheet1!B2:B6,MATCH(B1,Sheet1!A2:A6,0))
=INDEX(Sheet1!C2:C6,MATCH(B1,Sheet1!A2:A6,0))
=INDEX(Sheet1!D2:D6,MATCH(B1,Sheet1!A2:A6,0))

 

[abignale]

Thank you for all of your help everyone. I ended up using a Vlookup formula. I managed to accomplish what I was setting out to. Thanks again!