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

"Sledge Bacon" wrote:

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 ???
--
English geezer living in the USA

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


"JLatham" wrote:

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.

"Sledge Bacon" wrote:

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 ???
--
English geezer living in the USA

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.

"Sledge Bacon" wrote:

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


"JLatham" wrote:

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.

"Sledge Bacon" wrote:

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 ???
--
English geezer living in the USA

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" wrote:

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.

"Sledge Bacon" wrote:

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


"JLatham" wrote:

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.

"Sledge Bacon" wrote:

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 ???
--
English geezer living in the USA

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






"JLatham" wrote:

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" wrote:

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.

"Sledge Bacon" wrote:

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


"JLatham" wrote:

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.

"Sledge Bacon" wrote:

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 ???
--
English geezer living in the USA

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.
--
English geezer living in the USA


"JLatham" wrote:

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.

"Sledge Bacon" wrote:

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 ???
--
English geezer living in the USA

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.

"Sledge Bacon" wrote:

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.
--
English geezer living in the USA


"JLatham" wrote:

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.

"Sledge Bacon" wrote:

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 ???
--
English geezer living in the USA