ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   pull data from sheet two, then fill in the data to sheet one (part (https://www.excelbanter.com/excel-worksheet-functions/7789-pull-data-sheet-two-then-fill-data-sheet-one-part.html)

Jim

pull data from sheet two, then fill in the data to sheet one (part
 
Hello,

I would like to pull data from sheet two, then fill in the data to sheet
one.

Here is what the data from sheet 2 looks like:

A B C D
A1100 Dan 14 311.85714
A1102 Jason 12 296.83333
A1103 Brian 1 518
A1104 John 57 267.36842
A1105 Beverly 0 0
A1106 Jennifer 0 0
A1107 Chad 35 342.6
A1108 Brenda 1 243
A1109 Tina 2 193.5
A1110 Marcus 23 237.34783
A1111 Kelly 28 244.92857
A1112 Jim 19 425.94737
A1113 Michael 58 236.81034
A1115 Amy 9 282.77778
A1116 Teena 28 346.39286
A1117 Laura 65 204.04615
A1118 Randy 34 236.67647
A1119 Laura 4 278.5

Column A is the phone extension
Column B is the name of the Rep
Column C is the number of calls taken
Column D is the talk time in seconds

Although the data in the columns will never change, the rows in which the
data fills may vary.

I would like to populate sheet one A1 with Dan, B1 with 14, and C1 with the
311.85714.

Thanks for the help.


R.VENKATARAMAN

use vlookup function
anycell in sheet1 type

=VLOOKUP("dan",Sheet2!$B$1:$D$18,2,FALSE)
note the data base is from B1 to D18 and not from A1. the third argument is
2

similary on the right cell to this (or an;y cell) type

=VLOOKUP("dan",Sheet2!$B$1:$D$18,3,FALSE)
the third argument is 3

you will get the hang of it and modify to suit you.
=================

if change jason for dan you get
Jim wrote in message
...
Hello,

I would like to pull data from sheet two, then fill in the data to sheet
one.

Here is what the data from sheet 2 looks like:

A B C D
A1100 Dan 14 311.85714
A1102 Jason 12 296.83333
A1103 Brian 1 518
A1104 John 57 267.36842
A1105 Beverly 0 0
A1106 Jennifer 0 0
A1107 Chad 35 342.6
A1108 Brenda 1 243
A1109 Tina 2 193.5
A1110 Marcus 23 237.34783
A1111 Kelly 28 244.92857
A1112 Jim 19 425.94737
A1113 Michael 58 236.81034
A1115 Amy 9 282.77778
A1116 Teena 28 346.39286
A1117 Laura 65 204.04615
A1118 Randy 34 236.67647
A1119 Laura 4 278.5

Column A is the phone extension
Column B is the name of the Rep
Column C is the number of calls taken
Column D is the talk time in seconds

Although the data in the columns will never change, the rows in which the
data fills may vary.

I would like to populate sheet one A1 with Dan, B1 with 14, and C1 with

the
311.85714.

Thanks for the help.




K.S.Warrier

Hi,
In sheet 2 ,put in cell
A1=sheet1!B1
B1=sheet1!C1
C1=sheet1!D1
Then select A1:C1 in sheet 2 and copy this down for getting the desired
result.
Thank you,
K.S.Warrier

"Jim" wrote:

Hello,

I would like to pull data from sheet two, then fill in the data to sheet
one.

Here is what the data from sheet 2 looks like:

A B C D
A1100 Dan 14 311.85714
A1102 Jason 12 296.83333
A1103 Brian 1 518
A1104 John 57 267.36842
A1105 Beverly 0 0
A1106 Jennifer 0 0
A1107 Chad 35 342.6
A1108 Brenda 1 243
A1109 Tina 2 193.5
A1110 Marcus 23 237.34783
A1111 Kelly 28 244.92857
A1112 Jim 19 425.94737
A1113 Michael 58 236.81034
A1115 Amy 9 282.77778
A1116 Teena 28 346.39286
A1117 Laura 65 204.04615
A1118 Randy 34 236.67647
A1119 Laura 4 278.5

Column A is the phone extension
Column B is the name of the Rep
Column C is the number of calls taken
Column D is the talk time in seconds

Although the data in the columns will never change, the rows in which the
data fills may vary.

I would like to populate sheet one A1 with Dan, B1 with 14, and C1 with the
311.85714.

Thanks for the help.


Max

Assuming you have a *unique* list of all the names
in col A in Sheet1, in A2 down, viz.:

Name Total Calls Total Talk Time
-------- -------------- --------------------
Amy
Beverly
Brenda
Brian
Chad
Dan
Jason
Jennifer
Jim
John
Kelly
Laura
Marcus
Michael
Randy
Teena
Tina

To sum up and fill in cols B and C, viz.
"Total Calls" and "Total Talk Time" cols

Put in B2:
=IF(ISNA(MATCH(TRIM(A2),Sheet2!B:B,0)),"",SUMIF(Sh eet2!B:B,TRIM(A2),Sheet2!C
:C))

Put in C2:
=IF(ISNA(MATCH(TRIM(A2),Sheet2!B:B,0)),"",SUMIF(Sh eet2!B:B,TRIM(A2),Sheet2!D
:D))

Select B2:C2 and fill down

Any unmatched names in col A will return blanks: ""
in cols C and D

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Jim" wrote in message
...
Hello,

I would like to pull data from sheet two, then fill in the data to sheet
one.

Here is what the data from sheet 2 looks like:

A B C D
A1100 Dan 14 311.85714
A1102 Jason 12 296.83333
A1103 Brian 1 518
A1104 John 57 267.36842
A1105 Beverly 0 0
A1106 Jennifer 0 0
A1107 Chad 35 342.6
A1108 Brenda 1 243
A1109 Tina 2 193.5
A1110 Marcus 23 237.34783
A1111 Kelly 28 244.92857
A1112 Jim 19 425.94737
A1113 Michael 58 236.81034
A1115 Amy 9 282.77778
A1116 Teena 28 346.39286
A1117 Laura 65 204.04615
A1118 Randy 34 236.67647
A1119 Laura 4 278.5

Column A is the phone extension
Column B is the name of the Rep
Column C is the number of calls taken
Column D is the talk time in seconds

Although the data in the columns will never change, the rows in which the
data fills may vary.

I would like to populate sheet one A1 with Dan, B1 with 14, and C1 with

the
311.85714.

Thanks for the help.





All times are GMT +1. The time now is 06:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com