Using the string contents of a cell in a formula

[mikewould]

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:

='EngineeringServerroductionReporting:[38391.xls]Tonnage'!B2

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

 

[bltseattle]

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 = "EngineeringServerroductionReporting:["&A1&"]Tonnage'!B2"

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

 

[mikewould]

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.

 

[johnwm]

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:

http://www.essexsteam.co.uk

 

[mikewould]

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.

 

[FalsePrecision]

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.

 

[EGT01]

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

 

[mikewould]

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

 

[Denial]

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.
[tt]
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
[/tt]
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

 

[johnwm]

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:

http://www.essexsteam.co.uk