+ Reply to Thread
Results 1 to 6 of 6

Dynamic reference to a worksheet on a different workbook

  1. #1
    Registered User
    Join Date
    11-14-2019
    Location
    Ventura CA
    MS-Off Ver
    2016
    Posts
    3

    Dynamic reference to a worksheet on a different workbook

    I have two workbooks called Source and Destination. Each have 12 tabs names January, February, etc

    I need a formula that will dynamically take the name of the worksheet the formula is on on the Destination Workbook and obtain data from the SAME named worksheet on the source workbook. I want to create all the formulas on the January worksheet of the Destination Workbook that will grab the data from the January worksheet of the Source workbook but when I copy that formula to the February worksheet, it will know to grab that data from the February tab of the Source worksheet. In other words, I don't want to type January in the formula but want it to KNOW to go to the worksheet name of the source workbook based on whatever the Destination tab name the formula is on. I assume it has to use the indirect function. I know the formula to get the Sheet name but am having trouble with the syntax to use in the formula to extract the data from the other workbook.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Dynamic reference to a worksheet on a different workbook

    Hi -

    I created the month names in column C of the Destination workbook and then referenced that column into the formulas in A. So, the formula looks like:

    =INDIRECT("[Source.xlsx]"&C2&"!$A$3")

    where C2 contains "January", C3 contains "February", C4 is "March", etc. So as you copy down, the formula references the tab name from the months listed in column C.

    Hope that helps.
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Registered User
    Join Date
    11-14-2019
    Location
    Ventura CA
    MS-Off Ver
    2016
    Posts
    3

    Re: Dynamic reference to a worksheet on a different workbook

    Thank you for this. It is closer to what I want but has a problem.

    One Clarification, your comment suggests you thought I wanted data on the January tab to change which tab from the Source data comes from when I "copy down". That is not what is intended. EVERYTHING on the Destination January tab should return data from the Source January tab. The goal is to be able to create all the formulas on the January tab and then be able to simply copy all the formulas to the February tab and the February tab will automatically get data from the Source February tab.

    1. Instead of putting all the months in column C, I put in a formula in A1 (=TRIM(MID(SUBSTITUTE(CELL("filename",A1),"]",REPT(" ",99)),2*99-98,99)) that returns the tab name and then used your formula but changed it to =INDIRECT("[Source.xlsx]"&A1&"!$A$3"). That worked for Cell A3 getting the data from the correct tab in the Source File. I then copy that same formula to the February tab and it again gets the correct data from the February tab of the Source Tab. So far so good.


    2. However, when I copy the formula in Destination/January Tab/C3 to January C4, it does not return the data from the Source file/Januar yTab/C4, it returns the data from Source file/January Tab C3. I assume that is because it is using indirect. Is there a way to copy down from the C3 formula and have it return the correct data from the source file. In my simple example, when I copy the formula in Destination January C3 (which correctly returns the data from Source/January C3) to Destination January C4, I want it to return the data from Source/January C4. Likewise, when I copy all the formulas from the January Tab on the February Tab, It should return the data but from the Source February tab.

    Your solution does the second part but not the first because C3 does not change when I copy down or across.

    I can't seem to upload a file or even a direct link here so here is the path dropbox.com/s/sla5gcpe5c1dmiw/Destination.xlsx?dl=0[/url] to the new file with the new formulas. This works for cell A3 only but when I "copy down or across" It does not change the source cell so all are the same.

    I hope that is clear and I thank you for any additional help you can provide.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,282

    Re: Dynamic reference to a worksheet on a different workbook

    I can't seem to upload a file
    If you are attempting to use the paperclip icon, it doesn't work for this website.
    The directions for uploading a file are given in the banner at the top of the page which begins "DeanMoray Fast answers need clear examples..."
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Dynamic reference to a worksheet on a different workbook

    Hi -

    Sorry - Out of town. I think this will do what you want if you put the tab name (like January, February, etc) in Cell A1 of each Destination Tab and then the filename in Cell B2 like this:

    [Source-1.xlsx]

    Then, use the following formula to generate the correct cell references:

    =INDIRECT("'"&$B$1&$A$1&"!"&ADDRESS(ROW(A3),COLUMN(A3)))

    The ADDRESS function allows you to create a generic cell address in text format that will update the rows and columns as you copy down or across.

    The CELL("filename",A1) function returns the path which you then have to trim out the extraneous information and so forth to just get the filename. If that's what you want, then put your formula into B1 to generate the filename. Otherwise, I would just keep it simple and put the text of the filename in there. I would also keep it as separate pieces (Filename, Tab, and formula) rather than one giant formula just for ease in debugging in the future if you ever have to change files or if you try to remember a year from now how you set this up. This will make it much easier.

    Hope this helps.

  6. #6
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Dynamic reference to a worksheet on a different workbook

    Oh - One other thing - the month names (or tab names) need to have an apostrophe after it, so, for example: January' or February' and so forth. Or, you could add apostrophe in the formula to help you not have to remember to do that if you change tab names in the future.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Is it possible with a dynamic workbook reference
    By jeppenedved in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-10-2019, 03:17 AM
  2. Reference to a workbook on a website with dynamic worksheet
    By Anna88 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-20-2015, 09:59 AM
  3. Dynamic reference to workbook
    By bj in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 09-06-2005, 03:05 PM
  4. [SOLVED] Dynamic reference to workbook
    By Misssy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  5. [SOLVED] Dynamic reference to workbook
    By bj in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 07:05 AM
  6. [SOLVED] Dynamic reference to workbook
    By Misssy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. Dynamic reference to workbook
    By Misssy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1