=CONCATENATE() help!

S

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?
:mad:
 
B

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
 
S

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top