# Data Extraction to another xls File

#### atzerv

##### New Member
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!

### 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
The image links you have provided are broken (they don't link to anything)

#### atzerv

##### New Member
hmmm... Sorry! I thought that they will be uploaded. I will copy the spreadsheets and I will paste them. Thanks

#### atzerv

##### New Member
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":
**************************************************************************************************************************
(I put the column names on top of each column to be easier to understand the formula)
**************************************************************************************************************************
 C D E F Test Execution Steps Expected Result Actual Result Test 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. Error F 45. Add on the following character "shdhsj" on the table for the selected fit A. Validation message should appear like "invalid entry" or "not accepted" A. Error F 46. 2 users used the same account A. Same result to both users A. Error F 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. Error F C. OK C. Error D. Expected not to be able to open a second account on the same PC. D. Error F

<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***

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

 C D E F G H I J K L Start Value: F End Value: F Test Execution Steps Expected Result Actual Result Test 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. Error F 45. Add on the following character "shdhsj" on the table for the selected fit A. Validation message should appear like "invalid entry" or "not accepted" A. Error F 46. 2 users used the same account A. Same result to both users A. Error F 0 B. New browser page should ask for username and password. B. Error F 0 D. Expected not to be able to open a second account on the same PC. D. Error F #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
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
Thank you very much! I will do it right away and then I will wait your feedback