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...
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
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
='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
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
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
RE: Using the string contents of a cell in a formula
h
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
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
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
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk