Importing data from one workbook to another

[valkyrn]

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!

 

[valkyrn]

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.

 

[sandy666]

you can try PowerQuery (Get&Transform)

with one file

Date	01/06/2019
failure mode 1	1
failure mode 2	4
failure mode 3	6
failure mode 4	8
failure mode 5	9
failure mode 6	12
failure mode 7	45
failure mode 8	3
failure mode 9	2
failure mode 10	1

with two more files

Date	01/06/2019	02/06/2019	03/06/2019
failure mode 1	1	37	2
failure mode 2	4	24	49
failure mode 3	6	4	29
failure mode 4	8	9	8
failure mode 5	9	3	45
failure mode 6	12	36	27
failure mode 7	45	31	45
failure mode 8	3	1	50
failure mode 9	2	2	1
failure mode 10	1	36	11

is that what you want?

 

[valkyrn]

you can try PowerQuery (Get&Transform)

with one file

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]01/06/2019[/COLOR]
failure mode 1	1
failure mode 2	4
failure mode 3	6
failure mode 4	8
failure mode 5	9
failure mode 6	12
failure mode 7	45
failure mode 8	3
failure mode 9	2
failure mode 10	1

<tbody>
</tbody>

with two more files

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

<tbody>
</tbody>

is that what you want?

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.

 

[sandy666]

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

 

[valkyrn]

@sandy666 - I will look once i get to work and i will keep you posted.

Thanks for the response.

 

[sandy666]

You are welcome