Importing data from one workbook to another

V

valkyrn

New Member
Joined
May 31, 2019
Messages
4
Hi all,

Sorry if this has been covered before but i cannot find the answer to my question...

Right, here is my scenario...

One workbook gets sent out everyday from production, the values change but the structure of the workbook stay intact. This data i would like to get imported into another excel workbook where we produce graphs from the production data.

e.g. The workbook that we use to populate the graphs lists all the failure modes in column A. The dates are listed horizontally where we would input all data from the production file on a daily basis.

Would anyone help me with a solution to do the following: -


  1. Import data using a file dialog screen to select the appropriate file.
  2. Transfer values from specific failure modes from production workbook to metrics workbook. This must go in a new column without altering the format.

Hopefully i have explained good enough! If you need more information, just ask :)

Example xlsx

Thanks in advance!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Forgot to mention - My example contains only one workbook, i have used tabs to try and get my point across instead of creating multiple files i.e. tab 1 = production and tab 2 = metrics.
 
Upvote 0
you can try PowerQuery (Get&Transform)

with one file

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]01/06/2019[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]failure mode 1[/td][td=bgcolor:#E2EFDA]1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]failure mode 2[/td][td]4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]failure mode 3[/td][td=bgcolor:#E2EFDA]6[/td][/tr]

[tr=bgcolor:#FFFFFF][td]failure mode 4[/td][td]8[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]failure mode 5[/td][td=bgcolor:#E2EFDA]9[/td][/tr]

[tr=bgcolor:#FFFFFF][td]failure mode 6[/td][td]12[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]failure mode 7[/td][td=bgcolor:#E2EFDA]45[/td][/tr]

[tr=bgcolor:#FFFFFF][td]failure mode 8[/td][td]3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]failure mode 9[/td][td=bgcolor:#E2EFDA]2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]failure mode 10[/td][td]1[/td][/tr]
[/table]

with two more files

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]01/06/2019[/td][td=bgcolor:#70AD47]02/06/2019[/td][td=bgcolor:#70AD47]03/06/2019[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]failure mode 1[/td][td=bgcolor:#E2EFDA]1[/td][td=bgcolor:#E2EFDA]37[/td][td=bgcolor:#E2EFDA]2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]failure mode 2[/td][td]4[/td][td]24[/td][td]49[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]failure mode 3[/td][td=bgcolor:#E2EFDA]6[/td][td=bgcolor:#E2EFDA]4[/td][td=bgcolor:#E2EFDA]29[/td][/tr]

[tr=bgcolor:#FFFFFF][td]failure mode 4[/td][td]8[/td][td]9[/td][td]8[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]failure mode 5[/td][td=bgcolor:#E2EFDA]9[/td][td=bgcolor:#E2EFDA]3[/td][td=bgcolor:#E2EFDA]45[/td][/tr]

[tr=bgcolor:#FFFFFF][td]failure mode 6[/td][td]12[/td][td]36[/td][td]27[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]failure mode 7[/td][td=bgcolor:#E2EFDA]45[/td][td=bgcolor:#E2EFDA]31[/td][td=bgcolor:#E2EFDA]45[/td][/tr]

[tr=bgcolor:#FFFFFF][td]failure mode 8[/td][td]3[/td][td]1[/td][td]50[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]failure mode 9[/td][td=bgcolor:#E2EFDA]2[/td][td=bgcolor:#E2EFDA]2[/td][td=bgcolor:#E2EFDA]1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]failure mode 10[/td][td]1[/td][td]36[/td][td]11[/td][/tr]
[/table]

is that what you want?
 
Upvote 0
sandy666 said:
you can try PowerQuery (Get&Transform)

with one file

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]01/06/2019[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]failure mode 1[/TD]
[TD="bgcolor: #E2EFDA"]1[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]failure mode 2[/TD]
[TD]4[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]failure mode 3[/TD]
[TD="bgcolor: #E2EFDA"]6[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]failure mode 4[/TD]
[TD]8[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]failure mode 5[/TD]
[TD="bgcolor: #E2EFDA"]9[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]failure mode 6[/TD]
[TD]12[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]failure mode 7[/TD]
[TD="bgcolor: #E2EFDA"]45[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]failure mode 8[/TD]
[TD]3[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]failure mode 9[/TD]
[TD="bgcolor: #E2EFDA"]2[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]failure mode 10[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


with two more files

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]01/06/2019[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]02/06/2019[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]03/06/2019[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]failure mode 1[/TD]
[TD="bgcolor: #E2EFDA"]1[/TD]
[TD="bgcolor: #E2EFDA"]37[/TD]
[TD="bgcolor: #E2EFDA"]2[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]failure mode 2[/TD]
[TD]4[/TD]
[TD]24[/TD]
[TD]49[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]failure mode 3[/TD]
[TD="bgcolor: #E2EFDA"]6[/TD]
[TD="bgcolor: #E2EFDA"]4[/TD]
[TD="bgcolor: #E2EFDA"]29[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]failure mode 4[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]8[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]failure mode 5[/TD]
[TD="bgcolor: #E2EFDA"]9[/TD]
[TD="bgcolor: #E2EFDA"]3[/TD]
[TD="bgcolor: #E2EFDA"]45[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]failure mode 6[/TD]
[TD]12[/TD]
[TD]36[/TD]
[TD]27[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]failure mode 7[/TD]
[TD="bgcolor: #E2EFDA"]45[/TD]
[TD="bgcolor: #E2EFDA"]31[/TD]
[TD="bgcolor: #E2EFDA"]45[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]failure mode 8[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]50[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]failure mode 9[/TD]
[TD="bgcolor: #E2EFDA"]2[/TD]
[TD="bgcolor: #E2EFDA"]2[/TD]
[TD="bgcolor: #E2EFDA"]1[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]failure mode 10[/TD]
[TD]1[/TD]
[TD]36[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]


is that what you want?
Click to expand...

Thanks for the reply, i will look into powerquery and see if it does what i want.

Would this method automatically sort the fields once setup? as multiple users have to compile reports. The reason i want to do this is to simplify the process.
 
Upvote 0
first: don't quote whole post. just enough to use: @sandy666 if you answer to me [Reply not Reply With Quotes]

it works this way:
  • you've a folder with production files (each file has different name of course)
  • as you said each day you add new file
  • so after add new file to the folder just right click on the green table and select refresh
  • and each time you'll get new column with new data
hope it's clear

nb. PowerQuery is required :)

what you mean: sort fields? which fields? I need detailed info
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
VLOOKUP (or VBA Alternative?) From another workbook, depending on Financial Year
Replies
2
Views
469
paun_shotts
P
Y
  • Question Question
Pivot Table deletes on opening of workbook
Replies
3
Views
231
cooleomter
C
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
858
Micron
M
M
  • Solved
Pull exchange data from another workbook
Replies
2
Views
76
Maniac189
M
E
  • Solved
Performance issue using a combination of dropdowns & lookups
Replies
9
Views
616
Akuini
Akuini

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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