Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: use cell value in a formula

  1. #1

    use cell value in a formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hi,

    here is a formula

    ='C:\Users\Lynx\Google Drive\mpppm\mpppmP\t2mpppmP\[Modul_Offline_KHB_PERT_T2.xls]BORANG PEREKODAN'!D3

    I need to substitute the T2 (in Modul_Offline_KHB_PERT_T2.xls) with a string value in a cell (say it the cell C1)

    The C1 shall be T1 or T2 or T3

    Help(s) most appreciated

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Do you mean?



    =INDIRECT("'C:\Users\Lynx\Google Drive\mpppm\mpppmP\t2mpppmP\[Modul_Offline_KHB_PERT_" & C1 & ".xls]BORANG PEREKODAN'!D3")


  3. #3
    INDIRECT with a closed workbook?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Good point.... thanks, Bob. I didn't think of that....


    .... so basically you can't use the formula I provided if your other workbook is closed.

    You can use a PULL function by Harlan Grove (which coincidentally was co-inspired by Bob Phillips )... http://numbermonger.com/2012/02/11/e...sed-workbooks/

    which works the same way as my formula, except you need to add the Function to a new module in your VB editor (ALT+F11). Then replace INDIRECT() with PULL()


  5. #5
    Thanks guys,
    I shall look into it.
    Will update soon

  6. #6
    INDIRECT worked flawlessly having the other workbooks open.

  7. #7
    I managed to paste the PULL function's vba in Module 1 of


    mailmerge05.xlsm which is the main file in which the said formula is in B8

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by lynx653 View Post
    I managed to paste the PULL function's vba in Module 1 of


    mailmerge05.xlsm which is the main file in which the said formula is in B8

    ... and it worked hopefully ?


  9. #9
    replacement of

    =INDIRECT("'[Modul_Offline_BM_"&C1&".xls]BORANG PEREKODAN'!D3")

    with

    =PULL("'[Modul_Offline_BM_"&C1&".xls]BORANG PEREKODAN'!D3")

    didn't help

  10. #10
    3 supporting files are added in the archive.


    https://www.dropbox.com/s/xji7gu4yx4...ello2.zip?dl=0


    Appreciate your help sir.

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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