You need to take a close look at the reference you're building up for use as
the INDIRECT().
Go ahead and open up both workbooks and set a regular reference to the cell
in the Truck Logs (10-08).xls file. Examine that closely. Now close that
Truck Logs (10-08).xls file. Notice that the formula changes to include the
full path. I'm not sure that the formula you show as being in A31 has the
full path. It's missing something at the beginning, either \\ or a drive
reference. I'm thinking it needs to look something like:
="C:\IFTA\Sledge_IFTA_2008\'[Truck Logs ("&$A$29&").xls]"&$A$30&"'!$L$33"
Now, you also have to remember that using that is always going to retrieve
data from the file image on disk, even if you have the file open. You are
probably going to need to 'create a rule' that either the referenced files
always have to be open or not. If you decide that all must be open, then you
can remove the path information from the formula being used for the
INDIRECT($A$31) parameter. You'd get #REF errors until they are opened.
Let me know if this gets you a step or two on down the road to a solution or
not.
JLatham said:
Let me think on this and try to set up the same situation you have there.
Little rushed at the moment, just wanted you to know I haven't abandonded you.
:
I have made some progess using your example, which is great, BUT I really
need to be able to get a single SUM= hyperlink cell reference need to
multiple cells in another workbook, whilst also substituting/translating the
$l$33 cell reference.
Here is what I have achived so far...............
in cell A29 10-008
in cell A30 4/26/1904 (substitues to 1578)
in cell A31
="IFTA\Sledge_IFTA_2008\'[Truck Logs ("&$A$29&").xls]"&$A$30&"'!$L$33"
The substitution for A29 & A30 works like a charm, however the Cell
reference L33 does NOT get substittued at all. I tried a SUM(INDIRECT($A$31))
but I get a #REF error even if the workwook is already open.
--
English geezer living in the USA
:
You can probably use INDIRECT() to solve your problem. You have 2 variables
to the values to be added: the month-day portion and the truck number. You
could choose from lists or just type them into cells. Lets say that the
month/day goes into cell $A$1 as text like 10-08 and the truck numbers are
text in $B$1 like 1565
in some other cells you can build up the paths to the cells to be added like
this:
="IFTA\Sledge_IFTA_2008\'[Truck Logs (" & $A$1 & ").xls] & $B$1 & "'!$M$33"
="IFTA\Sledge_IFTA_2008\'[Truck Logs (" & $A$1 & ").xls] & $B$1 & "'!$P$33"
for example's sake we'll say those two formulas are in A2 and A3
Then your SUM() formula can become:
=SUM(INDIRECT($A$2),INDIRECT($A$3))
Hope this helps some.
:
My dilema is that I have a cumulative 3 month rolling report that takes input
from 3 seperate workbooks that contain 12 worksheets each.
The cumulative report cells have multiple hyperlinks (up to 9) back to the
individual workbooks. eg.
=SUM(IFTA\Sledge_IFTA_2008\[Truck Logs (10-08).xls]1565'!$M$33,
IFTA\Sledge_IFTA_2008\[Truck Logs (10-08).xls]1565'!$P$33)
I need to be able to substitute in both the month (10-08) and also the truck
number (1565). If I can code the month & truck number onto the worksheet say
in $A$30 and $A$31 I can reduce my hardcoding hyperlinks by a massive margin.
I keep seeing examples of VLOOKUP/INDIRECT but can't get the formulas correct.
Am I barking up the wrong tree ???