=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" <shfcook.1v91im_1126533990.1082@excelforum-nospam.com> wrote in message
news:shfcook.1v91im_1126533990.1082@excelforum-nospam.com...
>
> 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]

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)

[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" <shfcook.1v972l_1126541149.906@excelforum-nospam.com> wrote in message
news:shfcook.1v972l_1126541149.906@excelforum-nospam.com...
>
> 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
>

[shfcook]

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