Data Extraction to another xls File

atzerv

New Member
Joined
Dec 10, 2013
Messages
9
Dear friends, I found this site (Extract all rows from a range that meet criteria in one column in excel | Get Digital Help - Microsoft Excel resource) with a very good formula and very helpful for my task that I have to complete.
I attached these two files just to ask help because I need to use this formula to bring the valued from merged Cells, back to a different XLS file.
So, my aim is to update automatically a spreadsheet with values from many different Excel files.

The first problem that I have to solve, is the Merged cells that return '0' value.
The second problem is how I will connect the "Data Extraction" xls file with the "Source File" xls file to get the results from there?


The results that you see at image 2 are from image 1 which is at the same workbook. The formula that I am using is the following (in order to work the formula press: Shift+CTRL+Enter):
=INDEX('2 Results'!$A$14:$F$25,SMALL(IF(($L$3<='2 Results'!$F$14:$F$25)*($L$4>='2 Results'!$F$14:$F$25),MATCH(ROW('2 Results'!$D$14:$D$25),ROW('2 Results'!$D$14:$D$25))),ROW('2 Results'!C1)),COLUMN('2 Results'!C1))


My aim is to extract the values from image 3 to image 2.


Could you help me with this please?


Thank you very much!



 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,455
The image links you have provided are broken (they don't link to anything)
 

atzerv

New Member
Joined
Dec 10, 2013
Messages
9
hmmm... Sorry! I thought that they will be uploaded. I will copy the spreadsheets and I will paste them. Thanks
 

atzerv

New Member
Joined
Dec 10, 2013
Messages
9
The following Tables displays the spreadsheet "2 Results" that has the values that we want to extract to the second spreadsheet with the name "Extract Data":
**************************************************************************************************************************
Spreadsheet "2 Results"
(I put the column names on top of each column to be easier to understand the formula)
**************************************************************************************************************************
CDEF
Test Execution StepsExpected ResultActual ResultTest
Result
26. Click the "X" icon of remove button.
Click "No" from the confirmation dialog.
Click remove button again and get "Yes" from the confirmation dialog.
A. Will pop up a dialog to prompt user for confirmation.A. ErrorF
45. Add on the following character "shdhsj" on the table for the selected fitA. Validation message should appear like "invalid entry" or "not accepted"A. ErrorF
46. 2 users used the same account A. Same result to both usersA. ErrorF
64. Login with user account.
65. Open a new browser, copy the URL from first browser to new browser.
66. Log off from the second browser.
67 Log in with a different account.
A. OK
B. New browser page should ask for username and password.B. ErrorF
C. OKC. Error
D. Expected not to be able to open a second account on the same PC. D. ErrorF

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>
**************************************************************************************************************************

The following table are the extract results to the spreadsheet with the name "Extract Data" from the first spread sheet named "2 Results":
The formula used to retrieve the data from the first spreadsheet to the second one is the following:
=INDEX('2 Results'!$A$14:$F$25,SMALL(IF(($L$3<='2 Results'!$F$14:$F$25)*($L$4>='2 Results'!$F$14:$F$25),MATCH(ROW('2 Results'!$D$14:$D$25),ROW('2 Results'!$D$14:$D$25))),ROW('2 Results'!C1)),COLUMN('2 Results'!C1))
***Press Ctrl + Shift + Enter in order to work***

Spreadsheet "2 Results"
**************************************************************************************************************************

CDEFGHIJKL
Start Value:F
End Value:F
Test Execution StepsExpected ResultActual ResultTest
Result
26. Click the "X" icon of remove button.
Click "No" from the confirmation dialog.
Click remove button again and get "Yes" from the confirmation dialog.
A. Will pop up a dialog to prompt user for confirmation.A. ErrorF
45. Add on the following character "shdhsj" on the table for the selected fitA. Validation message should appear like "invalid entry" or "not accepted"A. ErrorF
46. 2 users used the same account A. Same result to both usersA. ErrorF
0B. New browser page should ask for username and password.B. ErrorF
0D. Expected not to be able to open a second account on the same PC. D. ErrorF
#NUM!#NUM!#NUM!#NUM!

<colgroup><col><col span="2"><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>


**************************************************************************************************************************




THE AIM of my task is to extract the data from the following Spreadsheet named "Source Spreadsheet" which belongs to different xls 2010 file. So, the data from "Source Spreadsheet" wish to be at the spreadsheet named "Extract Data" of the previous file.
When that established, then I will be able to extract more data from other files, too.
Thanks!
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,455
I'm unsure of the task you are trying to accomplish. Are you saying that the formula you have is not providing the results that you expect? If so where is the formula failing? Can you provide a before and after. You can download this Free Add-In in order to post data on the message board in a more readable format. This will allow others users on the forum to better understand your issue:

Ask Mr. Excel - VBA HTML Maker
 

atzerv

New Member
Joined
Dec 10, 2013
Messages
9
Thank you very much! I will do it right away and then I will wait your feedback
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,959
Messages
5,508,404
Members
408,681
Latest member
JustinJ

This Week's Hot Topics

Top