0

I am looking up one workbook (Figuresmaster) to pull financial figures into another workbook (AreaBudgets). I have the below lookup formula working (in AreaBudgets).

=IF(ISNA(
    VLOOKUP($C15,'(directory_path)\[Figuresmaster.xlsx]JULY2015'!$1:$1048576,12,FALSE)),
    0,
    VLOOKUP($C15,'(directory_path)\[Figuresmaster.xlsx]JULY2015'!$1:$1048576,12,FALSE))

I would like to now have a way of automatically finding the right month's sheet from workbook (Figuresmaster) instead to typing it into the formulas; e.g., a replacement for July2015 in above formula).  July2015 is the sheet name.  I need to be able to add sheets into Figuresmaster and not affect the formulas.

I am hoping for a formula to direct to either the sheet name July2015 or a cell, say A1, in each sheet.

I hope this makes sense, as I need this sheet to work.

5
  • 1
    It’s not crystal clear; especially not the second-to-last sentence.  What is “the right month’s sheet”?  I am confused by the fact that you are talking about “July2015” even though you have posted this in August 2015.  Do you mean the previous month’s sheet?  Do you mean that you want to put the Figuresmaster sheet name (July2015) into a cell in AreaBudgets, and then have all the formulas in AreaBudgets use it?  And what are you saying about “A1 in each sheet”? Aug 13, 2015 at 19:10
  • Your formula is (essentially) =IF(ISERROR(something), 0, that_same_something).  That can be hard to maintain (i.e., to modify, when needed, while maintaining consistency), especially if somebody else needs to take responsibility for your files.  That can be simplified to =IFERROR(something, 0). Aug 13, 2015 at 22:43
  • In the workbook (Figuresmaster) i have a worksheet for every months figures. i would like be able to get workbook (Areabudgets) to automatically pick up which sheet it wants to reference from. instead of having to type each month into the formula's. =IF(ISNA( VLOOKUP($C15,'(directory_path)[Figuresmaster.xlsx]JULY2015'!$1:$1048576,12,FALSE)), 0, VLOOKUP($C15,'(directory_path)[Figuresmaster.xlsx]JULY2015'!$1:$1048576,12,FALSE))
    – Rob Tully
    Aug 28, 2015 at 22:13
  • essentially replacing JULY2015 in the formula above with a look up function to find the right worksheet.
    – Rob Tully
    Aug 28, 2015 at 22:15
  • Am i able to attach an example of the 2 workbooks on here?
    – Rob Tully
    Aug 28, 2015 at 22:16

0

You must log in to answer this question.

Browse other questions tagged .