Hyperlink varaible substitution

Hyperlink varaible substitution - How To

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sledge Bacon - 22 May 2008 13:47 GMT

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 ???

Signature

English geezer living in the USA

Reply to this Message

JLatham - 22 May 2008 15:40 GMT

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.
[quoted text clipped - 11 lines]
> I keep seeing examples of VLOOKUP/INDIRECT but can't get the formulas correct.
> Am I barking up the wrong tree ???

Reply to this Message

Sledge Bacon - 27 May 2008 17:22 GMT

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.

Signature

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
[quoted text clipped - 28 lines]
> > I keep seeing examples of VLOOKUP/INDIRECT but can't get the formulas correct.
> > Am I barking up the wrong tree ???

Reply to this Message

JLatham - 28 May 2008 00:21 GMT

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
[quoted text clipped - 46 lines]
> > > I keep seeing examples of VLOOKUP/INDIRECT but can't get the formulas correct.
> > > Am I barking up the wrong tree ???

Reply to this Message

JLatham - 28 May 2008 15:13 GMT

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.

> 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.
[quoted text clipped - 49 lines]
> > > > I keep seeing examples of VLOOKUP/INDIRECT but can't get the formulas correct.
> > > > Am I barking up the wrong tree ???

Reply to this Message

Sledge Bacon - 28 May 2008 15:54 GMT

I put the FULL pathname in on the worksheet and it still acts the same. If I
do a Hyperlink using the mouse and swapping windows it still puts a shortened
pathname in. Result's are still the same.

Whilst debugging I have been using the Tools,Formula Auditing, Evaluate
Formula feature. It shows the translation of the first two varables
correctly, moves onto to the actual cell reference !$L$33 but NOT interpret
that what so ever. It seems as though the hyperlink reference is substitued
but NOT interpreted therafter.

Seeing that I did a INDIRECT to see if I could get the hyperlink to be fully
interpreted but get a #REF error. (Even with the workbook open ?)

BTW, I assume the ! in the cell reference !$L$33 determines it's a cell
reference in the way that a & is a concat character ??

> You need to take a close look at the reference you're building up for use as
> the INDIRECT().
[quoted text clipped - 70 lines]
> > > > > I keep seeing examples of VLOOKUP/INDIRECT but can't get the formulas correct.
> > > > > Am I barking up the wrong tree ???

Reply to this Message

JLatham - 28 May 2008 18:04 GMT

AHA!! I think the problem may be the date in A30, which is 4/26/1904 and
that you say substitutes to 1578 in the formula. I'm betting your sheet name
in the workbook is not 1578.

See if changing that portion of your formula to be like this helps any:
from &$A$30&
to & Text($A$30,"m/d/yyyy") &

may have to play with the date formatting part of that to get the same as
the name of the tab you want in the workbook, but should be able to do it, I
think.

> I put the FULL pathname in on the worksheet and it still acts the same. If I
> do a Hyperlink using the mouse and swapping windows it still puts a shortened
[quoted text clipped - 86 lines]
> > > > > > I keep seeing examples of VLOOKUP/INDIRECT but can't get the formulas correct.
> > > > > > Am I barking up the wrong tree ???

Reply to this Message

Sledge Bacon - 28 May 2008 18:55 GMT

The sheet name is QTR 2, so you are spot on there.

The A30 reference being 1578 but getting translated to 4/26/1904, don't you
just love Gregorian ! , had me thinking too. So I amended A30 call to 1578"
The " stops it being transposed to numerical/date association. Used the LEFT
function in the Hyperlink to remove the " from cell A30, thus keeping it
textural.

So the Amended A31 reference hyperlink is......

="\\ILLINISERVER\Users\Liz Gerlach\My
Documents\IFTA\Sledge_IFTA_2008\'[Truck Logs ("&$A$29&").xls]" &
LEFT($A$30,4) & "'!$L$33"

Using the Evaluate formula function I can see the variables get substitued
(correctly) for each seperate step, BUT after substituting the $A$30 to 1578
it just concatenates the !$l$33 cell reference to the end without
re-interpreteing the Hyperlink in it's now fully formed state.

Signature

English geezer living in the USA

> AHA!! I think the problem may be the date in A30, which is 4/26/1904 and
> that you say substitutes to 1578 in the formula. I'm betting your sheet name
[quoted text clipped - 98 lines]
> > > > > > > I keep seeing examples of VLOOKUP/INDIRECT but can't get the formulas correct.
> > > > > > > Am I barking up the wrong tree ???

Reply to this Message

JLatham - 29 May 2008 01:16 GMT

I'm getting a little lost here. First question: is it working now or not? I
presume not??

If you open the other book and just set up a manual link to it normally,
what does that formula look like?

I'll probably respond to an earlier post of yours next time, just to get the
indentation back to the left some.

Any way you could send the workbook where you want all this to happen in
along with one of the workbooks to be referenced to me as email attachments?
If so, this (without spaces) is a good address; HelpFrom @ jlathamsite. com

> The sheet name is QTR 2, so you are spot on there.
>
[quoted text clipped - 117 lines]
> > > > > > > > I keep seeing examples of VLOOKUP/INDIRECT but can't get the formulas correct.
> > > > > > > > Am I barking up the wrong tree ???

Reply to this Message

Sledge Bacon - 29 May 2008 14:11 GMT

Will be sending a mail titled "SledgeBacon Excel Help Reqd - From Microsoft
excel forum" to you. I shall detail more in there

The formula is not working. When I make a normal link I get
=SUM('[Truck Logs (10-08).xls]1578'!$L$33)

I am very appreciative of the assistance you have already provided me with.

Signature

English geezer living in the USA

Reply to this Message

JLatham - 29 May 2008 18:03 GMT

I'll keep an eye out for the email, and knowing what your formula should look
like helps. Knowing that the worksheet name is actually 1578 is a big plus
in my understanding.

> Will be sending a mail titled "SledgeBacon Excel Help Reqd - From Microsoft
> excel forum" to you. I shall detail more in there
[quoted text clipped - 36 lines]
> > > I keep seeing examples of VLOOKUP/INDIRECT but can't get the formulas correct.
> > > Am I barking up the wrong tree ???

Reply to this Message