Removing Volatility

[Monte75]

I am using an OFFSET() combined with a VLOOKUP() function in the formul
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 budge
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 othe
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 dat
instead of defaulting to an error? Is there some other workaround?
am using Excel 200

 

[RagDyer]

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]

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]

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

 

[RagDyeR]

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


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]

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

 

[RagDyer]

Appreciate the feed-back.