Results 1 to 4 of 4

Thread: Formula help

  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm looking for some help on how best to approach a formula problem I have. Since the sheet I'm working with contains sensitive data, I can not share it so I'll do my best to describe it.

    Below is a sample of the formula, but I know this is not going to work for a couple reasons. First off it's not that efficient, secondly it would require to be updated at least twice a year and that's what I'm trying to avoid. I've considered a VBA function to do this but I'm thinking this might be more of a problem since there will be close to 100 or more of these types of formulas. I'm thinking these function based formulas will constantly be calculating causing the sheet to run very slowly which I'd like to avoid

    In the below formula, row 2 is a cell that indicates that the month showing in row 3 is either actual data or 1 - 6 possible forecast. Row 3 is the month which is used in the look up formulas. Four of the 6 forecast sheets have a constant name (30,60,90,120 day sheets), but each year a new annual budget is added and new assessments are added and these sheet names will change each year. Due to the need to keep historical data, I can't rename sheets each year as new ones are added. This would mean having to update over 100+ formulas at least twice a year if not more.

    Is it possible to type the name of the sheet I need the formula to look up in a cell and have the formula read that cell and know that it needs to look at either the 2009 Annual Budget sheet or know that it needs to read from the Mar-09 Asmt sheet? I'm not sure if there is a worksheet function that allows this capability. If not, I'm sure I can make it work using a VBA function but again I'm concerned that with over 100+ of these, it may cause the sheet to run very slow.

    K2 = Actual data or Forecast data (directs formula to which sheet to look at)
    K3 = month to look up
    column A = lookup vector
    column GF = result vector

    =IF(K2="Actual",Lookup(K3,Actuals!A5:A124,Actuals! GF5:GF124),IF(K2="30 Day",Lookup(K3,30 Day!A5:A124,30 Day!GF5:GF124),IF(K2="60 Day",Lookup(K3,60 Day!A5:A124,60 Day!GF5:GF124),IF(K2="90 Day",Lookup(K3,90 Day!A5:A124,90 Day!GF5:GF124),IF(K2="120 Day",Lookup(K3,120 Day!A5:A124,120 Day!GF5:GF124),IF(K2="2009-Annual Budget",Lookup(K3,2009-Annual Budget!A5:A124,2009-Annual Budget!GF5:GF124),Lookup(K3,Mar-09 Asmt!A5:A124,Mar-09 Asmt!GF5:GF124)))))))

    Any thoughts on how best to approach this scenario would be appreciated. Thanks in advance!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    =Lookup(K3,INDIRECT("'"&K2&"'!A5:A124"),INDIRECT(" '"&K2&"'!GF5:GF124"))

    This requires that K2 contains the exact name of the sheet to be used for the lookup.

  3. #3
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='791133' date='28-Aug-2009 09:00']Try

    =Lookup(K3,INDIRECT("'"&K2&"'!A5:A124"),INDIRECT(" '"&K2&"'!GF5:GF124"))

    This requires that K2 contains the exact name of the sheet to be used for the lookup.[/quote]


    Hans - Thanks for the quick reply. I tried indirect but was not having any luck. I think my problem was not using the first set of quotes you included which was "'". I'll give this a try.

  4. #4
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='791133' date='28-Aug-2009 09:00']Try

    =Lookup(K3,INDIRECT("'"&K2&"'!A5:A124"),INDIRECT(" '"&K2&"'!GF5:GF124"))

    This requires that K2 contains the exact name of the sheet to be used for the lookup.[/quote]


    That did the trick Hans. Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •