=CONCATENATE() help!

[shfcook]

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?

 

[Bernie Deitrick]

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]

Hi, thanks for your interest. I was trying to acheive this kind o
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

 

[Bernie Deitrick]

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]

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