×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Using the string contents of a cell in a formula

Using the string contents of a cell in a formula

Using the string contents of a cell in a formula

(OP)
I'm working on a production report. I have a large number of small excel sheets from all departments of the mine, and I want to pull information from them into my production report.

I have a column of file names (based on dates) like 38391.xls

Instead of typing:

='EngineeringServer:ProductionReporting:[38391.xls]Tonnage'!B2

I would like to use the contents of a cell from my filenames column in place of the filename...

RE: Using the string contents of a cell in a formula

Try the INDIRECT function.  It returns the value from a reference that is provided in the form of a text string.

Something like this:
if cell A1 has value "38391.xls" then (using the text concatenation ampersand)

Cell B1 = "EngineeringServer:ProductionReporting:["&A1&"]Tonnage'!B2"

Then cell C1 = INDIRECT(B1) should return the value you want.

RE: Using the string contents of a cell in a formula

(OP)
I can't seem to get that to work either.  Here's a formula that works to return the piece of information that I need:

='PUBLIC:_Production Reporting:[38391.xls]Day Totals'!$B$2

The cell B5 contains the text:
"38391.xls"

cell C5 contains:
="'PUBLIC:_Production Reporting:["&B5&"]Day Totals'!$B$2"

as per your suggestion, I tried:
=INDIRECT(C5)
as well as
=INDIRECT(C5,true)

Both of which result in a #REF.   I fiddled with a few variants on the placement of the single tics, but to no avail.

RE: Using the string contents of a cell in a formula

The down side of INDIRECT is that the other file must be open. Excel help says:
If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value

There has been a recent discussion on updating external links in Thread770-115409

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting

UK steam enthusiasts: www.essexsteam.co.uk

RE: Using the string contents of a cell in a formula

(OP)
Hmmm. Perhaps I will have to concoct another way of doing this task.  At this time next year, there will be over 500 tiny excel sheets to draw from for the annual production report.  Clearly, opening all of them to update the master worksheet is not going to be fun.    

The problem is that I don't think Access is an option, because it is the operators themselves and their shift bosses who are entering the data into the sheets...

The daily reports don't change after they are saved, but I don't want to create a data entry job where one didn't exist before.  I'm at a loss here.

RE: Using the string contents of a cell in a formula

Why don't you open each worksheet, one at a time, copy its contents onto a "master" worksheet, so that each of the 500 worksheets is copied one below the other? If the rows are numbered consistantly, you can write a few simple formulas to pull all the data together.

RE: Using the string contents of a cell in a formula

See if any of these suggestions help...
http://www.excelforum.com/search.php?searchid=75812

I used the search terms <indirect closed file> to get the above listing.  Looks like other options include using VBA; looks like there may be an add-in program INDIRECT.EXT that might work or you could try the MS Query add-in that's in Excel, though I'm not familiar with how it works I think this is found under menu item Data\Get External Data.

As another thought, refering to your second post where you show your concatenated formula in cell C5, you could copy C5 and paste special\values into another cell then just add the "=" to the front end.  For that to be of any value for a large number of source file cells, you would need to have them together so you could copy and paste as a block then you could use the find\replace feature to add the "=".

Good Luck

RE: Using the string contents of a cell in a formula

(OP)
Thanks for the help.  I think I've almost licked it.  I'm close to getting that "man, I could  *sell*  this" feeling.  

I've ended up using INDIRECT along with find/replace to change all my VLOOKUPs to point to the right files (brute force), and I made an extra sheet with all the running totals on it (good call falseprecision)

The next thing on the to-do list will be a mill reconciliation.  I'm definately going to look into indirect.ext before that comes around.  

Thanks again.

Mike

RE: Using the string contents of a cell in a formula

Here is a bit of VBA code that I grabbed off John Walkenbach's EXCELent web site a few years ago.  It returns the value contained in a given cell of a given worksheet of a given CLOSED workbook at a given path.  The key is its use of the XLM macro.

Function GetValue(Path, File, Sheet, Ref)
'
'  Retrieves a value from a closed workbook.
'
'  Code obtained from http://j-walk.com/ss/excel/tips/tip82.htm
'  by DeNial in September 2002, then modified slightly.
'
Dim arg As String
'
'  Make sure the file exists.
'
If Right(Path, 1) <> "\" Then Path = Path & "\"
If Right(LCase(File), 4) <> ".xls" Then File = File & ".xls"
If Dir(Path & File) = "" Then
    GetValue = "File Not Found"
    Exit Function
End If
'
'  Create the argument.
'
arg = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
      Range(Ref).Range("A1").Address(, , xlR1C1)
'
'  Execute an XLM macro to do the deed.
'
GetValue = ExecuteExcel4Macro(arg)
'
End Function

I have used this successfully on Excel-97 and Excel-2002.  However I have not yet tried to apply it in a case where it has to "reach out" across a network to a different server.  If you try it, let me know how you go.

(One possible drawback to this approach is that presumably Microsoft will drop support for XLM macros at some stage.)

HTH

RE: Using the string contents of a cell in a formula

Did you look at the referred thread I offered earlier? The issues discussed and solutions offered in Thread770-115409 would appear to do just what you want

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting

UK steam enthusiasts: www.essexsteam.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources