Help with duplicating or extracting data from a database table

[lawaf]

Not sure what the best way to accomplish this is but I have a master database table which has all the information I want on an employee. I want to create a new sheet which only includes certain parts of the master spreadsheet data. I want the data on Sheet 2 to change when new data is entered or data is changed on the master database. I though a pivot table might be the answer but I don't need to summarize the data. I am using Excel 2007. Brief visualization is below:

Master DataBase
Columns
Name Unit Hire Date Transfer Date Position Status Phone email Eval Date


Sheet 2
Columns
Name Unit Hire Date Transfer Date Postion Status

Thanks!

 

[Robert Mika]

Not sure what the best way to accomplish this is but I have a master database table which has all the information I want on an employee. I want to create a new sheet which only includes certain parts of the master spreadsheet data. I want the data on Sheet 2 to change when new data is entered or data is changed on the master database. I though a pivot table might be the answer but I don't need to summarize the data. I am using Excel 2007. Brief visualization is below:

Master DataBase
Columns
Name Unit Hire Date Transfer Date Position Status Phone email Eval Date


Sheet 2
Columns
Name Unit Hire Date Transfer Date Postion Status

Thanks!

Hello and welcme to MrExcel.
Can you psot some data from Sheet1 and desired result from on Sheet2?
Please see below on how to post data.

 

[lawaf]

Sheet1

Name Unit Hire Date Transfer Date Position Status Phone email Eval Date
Jones, John Division 1

 

[lawaf]

Below is a sample of whr I am trying to do. I though of copying the sheet and then deleting the unecessary colums then all I need to do is to connect ot my main Database Sheet 1 (which has a total of about 30 col of data). Sheet 2 is only the data relating to personnel assignments and replacements.<STRONG><BR><BR>Sheet 1 <BR><BR>Name           Unit           HireDate      TranDate    Position        Status          Phone         email                Eval Date    Unit Code<BR></STRONG>Jones, John  Division 1  1-Oct-12     1-Oct 13     Director        Filled          123-4567    <A href="mailto:Jones.1@org">Jones.1@org</A>     1-Oct-11   11223344<BR><BR>Smith, Andy  Unit 1      1-Jul-11       1-Dec-12    Coordinator   30-60 Loss  123-3312    <A href="mailto:Smth.a@org">Smth.a@org</A>      1-Jul-12    22335566<BR><BR><BR><STRONG>Sheet 2<BR></STRONG><BR><STRONG>Unit Code         Name                   Unit              HireDate    TransferDate   Postion          Status    <BR></STRONG>11223344        Jones, John        Division1     1-Oct-12      1-Oct-13        Director         Filled<BR><BR>22335566        Smith, Andrew    Unit 1         1-Jul-11       1-Dec-12        Coordinator     30-60 Day Loss   

 

[Robert Mika]

Below is a sample of whr I am trying to do. I though of copying the sheet and then deleting the unecessary colums then all I need to do is to connect ot my main Database Sheet 1 (which has a total of about 30 col of data). Sheet 2 is only the data relating to personnel assignments and replacements.

Sheet 1

Name Unit HireDate TranDate Position Status Phone email Eval Date Unit Code
Jones, John Division 1 1-Oct-12 1-Oct 13 Director Filled 123-4567 Jones.1@org 1-Oct-11 11223344

Smith, Andy Unit 1 1-Jul-11 1-Dec-12 Coordinator 30-60 Loss 123-3312 Smth.a@org 1-Jul-12 22335566


Sheet 2

Unit Code Name Unit HireDate TransferDate Postion Status
11223344 Jones, John Division1 1-Oct-12 1-Oct-13 Director Filled

22335566 Smith, Andrew Unit 1 1-Jul-11 1-Dec-12 Coordinator 30-60 Day Loss

Copy only Headers that you need into second sheet and the use this formula, drag down and accross

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
1	Name	Unit	HireDate	TransferDate	Position	Status	Phone	email	Eval	Unit code
2	Jones, John	Division 1	1-Oct-12	1-Oct 13	Director	Filled	123-4567	Jones.1@org	1-Oct-11	112233
3	Smith, Andy	Unit 1	1-Jul-11	1-Dec-12	Coordinator	30-60 Loss	123-3312	Smth.a@org	1-Jul-12	22335566

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Excel 2010

	A	B	C	D	E	F	G
1	Unit code	Name	unit	HireDate	TransferDate	Position	Status
2	112233	Jones, John	Division 1	1-Oct-12	1-Oct 13	Director	Filled
3	22335566	Smith, Andy	Unit 1	1-Jul-11	1-Dec-12	Coordinator	30-60 Loss

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula A2 =INDEX(Sheet1!$A$2:$J$27,ROWS($A$1:A1),MATCH(A$1,Sheet1!$A$1:$J$1,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[lawaf]

So I tried this but it did not work. I am using Excel 2007. Does it matter if the columns are not in the same order? Below is the link at my dropbox to the file. On it you can see my main Personnel Database and the Assignment Data where I want to copy the data to. I have a couple spreadsheets I need to do a similar thing with. Thanks http://dl.dropbox.com/u/10444513/UMT_Personnel_Roster.xlsx

 

[Robert Mika]

So I tried this but it did not work. I am using Excel 2007. Does it matter if the columns are not in the same order? Below is the link at my dropbox to the file. On it you can see my main Personnel Database and the Assignment Data where I want to copy the data to. I have a couple spreadsheets I need to do a similar thing with. Thanks http://dl.dropbox.com/u/10444513/UMT_Personnel_Roster.xlsx

Try this in A1 of Assigment Data sheet:
=INDEX(Table_UMT_Personell_Roster,ROWS($A$1:A1),MATCH(A$1,Table_UMT_Personell_Roster3[#Headers],0))

 

[lawaf]

When I placed it in the A1 cell of my Assignment Data table it returned a 0