I am using an OFFSET() combined with a VLOOKUP() function in the formula
below to allow me to pluck budget information from a 12 month budget.
The OFFSET() allows me to specify a month (in $B$1) and get the budget
through that month. I am pulling from many different files.

=SUM(OFFSET([asite_162.xls]Bud!$B$12:$M$60,4,0,1,VLOOKUP(LEFT($B$1,3),Sheet2! $B$3:$C$14,2,FALSE)))

My problem is that I get a #VALUE! error when I don't have the other
workbooks open. I know that this is because the formula is volatile.
Is there a way for me to tell the formula to remember the last data
instead of defaulting to an error? Is there some other workaround? I
am using Excel 2003


--
Monte75
------------------------------------------------------------------------
Monte75's Profile: http://www.excelforum.com/member.php...fo&userid=2171
View this thread: http://www.excelforum.com/showthread...hreadid=472789

Does this work for you:

=SUM([asite_162.xls]Bud!$B$16:INDEX([asite_162.xls]Bud!$B$16:$M$16,VLOOKUP(L
EFT($B$1,3),Sheet2!$B$3:$C$14,2,0)))

?
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Monte75" wrote in
message ...

I am using an OFFSET() combined with a VLOOKUP() function in the formula
below to allow me to pluck budget information from a 12 month budget.
The OFFSET() allows me to specify a month (in $B$1) and get the budget
through that month. I am pulling from many different files.


=SUM(OFFSET([asite_162.xls]Bud!$B$12:$M$60,4,0,1,VLOOKUP(LEFT($B$1,3),Sheet2
!$B$3:$C$14,2,FALSE)))

My problem is that I get a #VALUE! error when I don't have the other
workbooks open. I know that this is because the formula is volatile.
Is there a way for me to tell the formula to remember the last data
instead of defaulting to an error? Is there some other workaround? I
am using Excel 2003


--
Monte75
------------------------------------------------------------------------
Monte75's Profile:
http://www.excelforum.com/member.php...fo&userid=2171
View this thread: http://www.excelforum.com/showthread...hreadid=472789

Not at this point -- I get a #NAME? error. I think it has something to
do with the formula being slightly off -- I'm trying to fix it now.


--
Monte75
------------------------------------------------------------------------
Monte75's Profile: http://www.excelforum.com/member.php...fo&userid=2171
View this thread: http://www.excelforum.com/showthread...hreadid=472789

Actually the name error was my fault. What I'm getting isn't correct,
however. If I understand the function correctly, OFFSET() allows me to
sum a whole range of numbers, such as columns A thru G, while the
INDEX() just pulls one cell. I need the range because I am adding
monthly numbers to make a YTD number. The formula lets me change the
Month in cell $B$1 and not have to change information in every single
formula.


--
Monte75
------------------------------------------------------------------------
Monte75's Profile: http://www.excelforum.com/member.php...fo&userid=2171
View this thread: http://www.excelforum.com/showthread...hreadid=472789

You're correct about Index() referencing a single cell, *BUT* ... that
single cell sets the range for the Sum() function.

Your formula starts the range to sum at B16, so:

=Sum(B16:M16)
Would add all 12 months ... right?

My formula starts the Sum() function at B16, and then indexes the entire
range (B16:M16), and then allows your Vlookup() function to set the
reference point within the index.

I'm assuming that your Vlookup() function returns the numbers 1 to 12,
depending on the month entered in B1.

So the formula ends up in a pseudo fashion of:

=Sum(B16 to wherever along B16:M16 that Vlookup tells it to stop at)
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------


"Monte75" wrote in
message ...

Actually the name error was my fault. What I'm getting isn't correct,
however. If I understand the function correctly, OFFSET() allows me to
sum a whole range of numbers, such as columns A thru G, while the
INDEX() just pulls one cell. I need the range because I am adding
monthly numbers to make a YTD number. The formula lets me change the
Month in cell $B$1 and not have to change information in every single
formula.


--
Monte75
------------------------------------------------------------------------
Monte75's Profile:
http://www.excelforum.com/member.php...fo&userid=2171
View this thread: http://www.excelforum.com/showthread...hreadid=472789

I don't know what happened this morning, but it works like a charm!
Thank you. I'll note the problem as solved in the title.


--
Monte75
------------------------------------------------------------------------
Monte75's Profile: http://www.excelforum.com/member.php...fo&userid=2171
View this thread: http://www.excelforum.com/showthread...hreadid=472789

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Monte75" wrote in
message ...

I don't know what happened this morning, but it works like a charm!
Thank you. I'll note the problem as solved in the title.


--
Monte75
------------------------------------------------------------------------
Monte75's Profile:
http://www.excelforum.com/member.php...fo&userid=2171
View this thread: http://www.excelforum.com/showthread...hreadid=472789