|
Removing Volatility
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 |
| All times are GMT +1. The time now is 03:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com