I'm trying to pull into one sheet, the values from the same 10 cells in
about 500 spreadsheets all stored in the same way.

Using =CONCATENATE() I can construct the formula which correctly
reference each cell in each file (all date named files), also using the
=TEXT() function to create all the file names. However I still need to
f2 then f9 each formula produced by the =CONCATENATE() function to get
the cell value pulled through from the relevant file. I don't want to
have to do this 5000 times, is there a shortcut that anyone knows?



--
shfcook
------------------------------------------------------------------------
shfcook's Profile: http://www.excelforum.com/member.php...o&userid=27169
View this thread: http://www.excelforum.com/showthread...hreadid=466779

shfcook,

You could use a macro to create the linking formulas. Post back with the form of the formulas that
you want to use, along with the logic that you use to create them, and I will post a macro for you.

HTH,
Bernie
MS Excel MVP


"shfcook" wrote in message
...

I'm trying to pull into one sheet, the values from the same 10 cells in
about 500 spreadsheets all stored in the same way.

Using =CONCATENATE() I can construct the formula which correctly
reference each cell in each file (all date named files), also using the
=TEXT() function to create all the file names. However I still need to
f2 then f9 each formula produced by the =CONCATENATE() function to get
the cell value pulled through from the relevant file. I don't want to
have to do this 5000 times, is there a shortcut that anyone knows?



--
shfcook
------------------------------------------------------------------------
shfcook's Profile: http://www.excelforum.com/member.php...o&userid=27169
View this thread: http://www.excelforum.com/showthread...hreadid=466779

Hi, thanks for your interest. I was trying to acheive this kind of
formula:

='F:\blah blah\blah blah\archive\[13-Jan-04.xls]SUMMARY'!$G$19

by using:

=CONCATENATE($C$2,TEXT(B26,"dd-mmm-yy"),$D$2,$E$2)

where C2= ='F:\blah blah\blah blah\archive\[
B26 is the variable date, all files have a date stamp name
D2 is .xls]SUMMARY'!
and E2 is $g$19

any help would be most gratefully received. (I am VBA literate also,
just not thought of way to code this)


--
shfcook
------------------------------------------------------------------------
shfcook's Profile: http://www.excelforum.com/member.php...o&userid=27169
View this thread: http://www.excelforum.com/showthread...hreadid=466779

Then that's pretty easy.

Select all the cells with your CONCATENATE formulas, and run this macro:

Sub TryNow()
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = myCell.Value
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"shfcook" wrote in message
...

Hi, thanks for your interest. I was trying to acheive this kind of
formula:

='F:\blah blah\blah blah\archive\[13-Jan-04.xls]SUMMARY'!$G$19

by using:

=CONCATENATE($C$2,TEXT(B26,"dd-mmm-yy"),$D$2,$E$2)

where C2= ='F:\blah blah\blah blah\archive\[
B26 is the variable date, all files have a date stamp name
D2 is .xls]SUMMARY'!
and E2 is $g$19

any help would be most gratefully received. (I am VBA literate also,
just not thought of way to code this)


--
shfcook
------------------------------------------------------------------------
shfcook's Profile: http://www.excelforum.com/member.php...o&userid=27169
View this thread: http://www.excelforum.com/showthread...hreadid=466779

thanks so much, some of the best things in life are the simplest!


--
shfcook
------------------------------------------------------------------------
shfcook's Profile: http://www.excelforum.com/member.php...o&userid=27169
View this thread: http://www.excelforum.com/showthread...hreadid=466779