Help with duplicating or extracting data from a database table

L

lawaf

New Member
Joined
Oct 29, 2012
Messages
5
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!
 
Sort by date Sort by votes
lawaf said:
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!
Click to expand...

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.
 
Upvote 0
Sheet1

Name Unit Hire Date Transfer Date Position Status Phone email Eval Date
Jones, John Division 1
 
Upvote 0
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   
 
Upvote 0
lawaf said:
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
Click to expand...


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

Excel 2010
ABCDEFGHIJK
NameUnitHireDateTransferDatePositionStatusPhoneemailEvalUnit code
Jones, John Division 1 1-Oct-12 1-Oct 13 Director Filled 123-4567 Jones.1@org 1-Oct-11
Smith, Andy Unit 1 1-Jul-11 1-Dec-12 Coordinator 30-60 Loss 123-3312 Smth.a@org 1-Jul-12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]112233[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]22335566[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​


Excel 2010
ABCDEFG

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Unit code[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]unit[/TD]
[TD="align: center"]HireDate[/TD]
[TD="align: center"]TransferDate[/TD]
[TD="align: center"]Position[/TD]
[TD="align: center"]Status[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]112233[/TD]
[TD="align: center"]Jones, John [/TD]
[TD="align: center"]Division 1 [/TD]
[TD="align: center"] 1-Oct-12 [/TD]
[TD="align: center"] 1-Oct 13 [/TD]
[TD="align: center"] Director [/TD]
[TD="align: center"] Filled [/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]22335566[/TD]
[TD="align: center"]Smith, Andy [/TD]
[TD="align: center"]Unit 1 [/TD]
[TD="align: center"] 1-Jul-11 [/TD]
[TD="align: center"]1-Dec-12 [/TD]
[TD="align: center"] Coordinator [/TD]
[TD="align: center"] 30-60 Loss [/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=INDEX(Sheet1!$A$2:$J$27,ROWS($A$1:A1),MATCH(A$1,Sheet1!$A$1:$J$1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
lawaf said:
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
Click to expand...

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))
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
help with data shared between tables
Replies
0
Views
236
The_Flapjack
T
Y
  • Question Question
Date and User stamp to show changes in a column that has linked Data from another Worksheet
Replies
6
Views
195
Yeft
Y
J
  • Question Question
Compare 2 Workbooks and add only the New Information to a Master Sheet
Replies
0
Views
298
jpaul123
J
Leorand
  • Solved
Excel VBA copy and duplicate data
Replies
7
Views
735
myall_blues
myall_blues
E
  • Solved
Copy a certain set of data from one sheet to another depending on the date typed in
2
Replies
10
Views
644
myall_blues
myall_blues

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top