Drag Down Formula, but only change worksheet number

  • Hello,
    I am currently working on an excel spread sheet that I am linking data from another workbook ='[CCS Client Case Eligibility Review Tool (Qtr2).xlsm]


    I want to drag down the same formula, but only change the worksheet name.


    example of work sheet: Weslaco (1), Weslaco (2), Weslaco (3), Weslaco (4), and etc...


    this is my formula that i am using:


    ='[CCS Client Case Eligibility Review Tool (Qtr2).xlsm]Weslaco (2)'!$G$8


    Thank You for your time.

  • Re: Drag Down Formula, but only change worksheet number


    If the source WB will also be open, you could use INDIRECT


    =indirect("'[CCS Client Case Eligibility Review Tool (Qtr2).xlsm]Weslaco ("&rows(1:1)&")'!$G$8")


    If the source will not be open, consider installing the Moreunc add-in which permits using INDIRECT on closed WB's
    [TABLE="width: 64"]

    [tr]


    [td]

    http://www.ashishmathur.com/tag/morefunc/

    [/td]


    [/tr]


    [/TABLE]

    Regards


    Ford