extracting data

ehsas69

Board Regular
Joined
Jan 10, 2004
Messages
221
I would like to fill the following worksheet based on data available in the differents file

I have sixteen months file which names as jan2005 to jun2005

i want formula or macro to be put in below workseet which will check the data in the sixteen months data and take the information from jan2005 a2 cell and put in b2 and a3 to c2 and a4 to e2 and b5 to f2 and a6 to g2 and a7 to h2 and a8 to i2 and for feb2005 the data to be filled in row 3.

thanks
Book1
ABCDEFGHI
1MONTHDATA1DATA2DATA3DATA4DATA5DATA6DATA7DATA8
2JAN'04
3FEB'04
4MAR'04
5APR'04
Sheet1
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
First, change the dates in Column A so that they're true date values. For example, enter the dates as follows...

Jan 2005
Feb 2005
Mar 2005
etc.

You can format these as desired. Then try the following formula...

B2, copied across and down:

=INDIRECT("'"&TEXT($A2,"mmmyyyy")&"'!"&CELL("address",INDEX($A:$A,COLUMNS($B2:B2)+1)))

Hope this helps!
 

ehsas69

Board Regular
Joined
Jan 10, 2004
Messages
221
Two question i have

what to put in address
it not checking other file

Regards,
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
Sorry, I misunderstood! I see now that the source data is contained in different files. In this case, try the following formula instead...

=INDIRECT("'["&TEXT($A2,"mmmyyyy")&".xls]Sheet1'!"&CELL("address",INDEX($A:$A,COLUMNS($B2:B2)+1)))

Change the sheet reference accordingly. Note that the source file must be opened. However, if you download and install the free add-in Morefunc.xll, you can use INDIRECT.EXT which doesn't require the source file to be opened.

Hope this helps!
 

ehsas69

Board Regular
Joined
Jan 10, 2004
Messages
221

ADVERTISEMENT

is this formula correct

=INDIRECT("'["&TEXT($A2,"mmmyyyy")&".xls]Sheet1'!"&CELL("C:\Documents and Settings\user\My Documents\jan2005",INDEX($A:$A,COLUMNS($B2:B2)+1)))
 

ehsas69

Board Regular
Joined
Jan 10, 2004
Messages
221
below is the screen shot of jan2005 file
Jan2005.xls
ABCD
1seriesbalance
2series12000
3series23000
4series34500
5series41000
6series56500
7series67500
8series79000
Sheet1
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697

ADVERTISEMENT

First, since INDIRECT requires the source file to be opened, there's no need to specify the path to the file. The formula should be...

=INDIRECT("'["&TEXT($A2,"mmmyyyy")&".xls]Sheet1'!"&CELL("address",INDEX($A:$A,COLUMNS($B2:B2)+1)))

Notice that "address" needs to remain as is. It doesn't need to be changed to anything else. Also, as mentioned previously, INDIRECT.EXT works with closed files.

Hope this helps!
 

ehsas69

Board Regular
Joined
Jan 10, 2004
Messages
221
thanks it works with few changes as follow

=INDIRECT.EXT("'["&TEXT($A3,"mmmyyyy")&".xls]Sheet1'!"&CELL("address",INDEX($B:$B,COLUMNS($B3:B3)+1)))

I have changed the data in file jan 2005 from b2 to b29. the problem is that the formula is not reconizing the value please help.

Thanks
 

ehsas69

Board Regular
Joined
Jan 10, 2004
Messages
221
thanks very much.It help.

I have one question.Is it necessary that all files has to be in same folder or I can extract the database from different folder.

Once again appreciate your help.
 

Forum statistics

Threads
1,136,508
Messages
5,676,270
Members
419,617
Latest member
Shane50GT

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
Top