1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Indirect Formula Excel Referencing Other Workbooks

Discussion in 'Business Applications' started by estyMonsty, Feb 19, 2019.

Thread Status:
Not open for further replies.
Advertisement
  1. estyMonsty

    estyMonsty Thread Starter

    Joined:
    Mar 22, 2017
    Messages:
    19
    Hi All,

    I am new to Indirect formulas and am trying to reference cells by workbook and sheet. The cells remain the same across all sheets.
    I've attached a copy of what I am trying to achieve. I've only tested on Sydney (refer to cell C4) and keep getting #REF!

    When referencing directly:
    =[MonthlyReportsSummaryTable_SYD_2019_V9.xlsx]Jan_2019!D16
    e.g. workbook name = I4 / sheet name = F1 / cell reference = D16

    With Indirect formula:
    =INDIRECT("["&$I$4&"]"&$F$1&"!"&C$15)

    What am I doing wrong?
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
  3. estyMonsty

    estyMonsty Thread Starter

    Joined:
    Mar 22, 2017
    Messages:
    19
    Work books are open.
    I think the issue is with the formula?
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    e.g. workbook name = I4 / sheet name = F1 / cell reference = D16

    With Indirect formula:
    =INDIRECT("["&$I$4&"]"&$F$1&"!"&C$15)

    C15 or D16

    I4 = =MonthlyReportsSummaryTable_SYD_2019_V9.xlsx
    F1 = Jan_2019
    D16 = ??? does that = C$15

    maybe load the spreadsheets , with sample data in

    both workbooks in the same directory on the PC
     
  5. estyMonsty

    estyMonsty Thread Starter

    Joined:
    Mar 22, 2017
    Messages:
    19
    C15 = D16 is in the other workbook [MonthlyReportsSummaryTable_SYD_2019_V9.xlsx]
    Both workbooks are saved in the same place.
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    so this works
    =[MonthlyReportsSummaryTable_SYD_2019_V9.xlsx]Jan_2019!D16

    and then you have in the sheet
    I4 contains MonthlyReportsSummaryTable_SYD_2019_V9.xlsx
    F1 contains Jan_2019
    C$15 contains D16

    and in some cell on that sheet you have

    =INDIRECT("["&$I$4&"]"&$F$1&"!"&C$15)

    and JAN_2019 - is text and not formatted as a date

    its working for me
     

    Attached Files:

  7. estyMonsty

    estyMonsty Thread Starter

    Joined:
    Mar 22, 2017
    Messages:
    19
    Amazing!! Thank you so much.
    JAN_2019 drop down list was formatted as custom so changed it to text and it worked.
     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    you are welcome
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1223330

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice