+ Reply to Thread
Results 1 to 4 of 4

Inserting new information in source document that can update destination documents

  1. #1
    Registered User
    Join Date
    01-10-2015
    Location
    Lexington, Virginia, United States of America
    MS-Off Ver
    2007
    Posts
    3

    Inserting new information in source document that can update destination documents

    I am setting up a construction cost file to be the source to be referenced by multiple cost estimating files for various model homes I intend to offer to build as a contractor. The intention is to be able to update the cost file with current prices that will be referenced by the destination files.

    I have discovered that the default of cell reference for an external file is absolute. When I insert new line with new information, this result in the reference to the wrong line. Experimenting with blank files, I see that if the external references are not absolute, the destination file will track the changed location as does the information in relative references to cells within a file. With both files open, if relative reference is =[Book1.xlsx]Sheet1!B6 without the $ to make absolute reference, the destination cell's formula will track the new location. However, if the destination file is closed when I make the insertion, it will not.

    I am looking for tips on how to update the source construction cost file so that it will by reference update the destination files that reference it for cost. If I do not insert new lines or fields, this works. However, to keep information organized, I need to insert information where it is logical. Since many destination files are going to reference the source files, having them all open is not practical.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inserting new information in source document that can update destination documents

    To reference information in external workbooks where the location of needed data moves around, you cannot link directly to cells. You will need to use LOOKUP functions to find the matching part number in one column and return the price from the adjacent column on the same row.

    Reliable functions I would recommend:

    VLOOKUP()
    INDEX(MATCH())
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-10-2015
    Location
    Lexington, Virginia, United States of America
    MS-Off Ver
    2007
    Posts
    3

    Re: Inserting new information in source document that can update destination documents

    Great tip on the Vlookup function. I have been making it work.

    However, screen save shows an example of it not working. I have entered
    =H60*VLOOKUP(F60,'[Material Cost.xlsx]Openings'!$A$12:$H$16,1,FALSE)
    H60-quantity to multiply vlookup value
    F60-the cell reference to text to look up value
    '[Material Cost.xlsx]Openings'-external file named "Material Cost.xlsx", sheet named "Openings",
    $A$12:$H$16-on that sheet table defined from cell A12 to H16 with absolute $ reference
    3-# of colume to enter value
    FALSE- Exact Reference
    Yet I get the dreaded #/N/A when I enter the formula. Help again will be appreciated.
    VLOOKUP Problem.jpg

    Thank you for previous tip about this feature.

    Eli

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inserting new information in source document that can update destination documents

    Quote Originally Posted by ELIofVA View Post
    Great tip on the Vlookup function. I have been making it work.

    However, screen save shows an example of it not working. I have entered
    =H60*VLOOKUP(F60,'[Material Cost.xlsx]Openings'!$A$12:$H$16,1,FALSE)
    H60-quantity to multiply vlookup value
    F60-the cell reference to text to look up value
    '[Material Cost.xlsx]Openings'-external file named "Material Cost.xlsx", sheet named "Openings",
    $A$12:$H$16-on that sheet table defined from cell A12 to H16 with absolute $ reference
    3-# of colume to enter value
    FALSE- Exact Reference
    Yet I get the dreaded #/N/A when I enter the formula. Help again will be appreciated.
    From your picture, it looks like the F60 value matches to the external document's column B. But your vlookup is anchored to column A, so that's never going to work.

    The first column in the VLOOKUP range is the range that is matched, so that must change to B, not A. Next the 3rd parameter is the number of the column in the indexed range to return a value from. If you change the range to B:C, then the third parameter will be 2 as column C has the price you want and column C is the second column in the indexed range B:C.

+ 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] Date/Time stamp:inserting the source book date & time into the destination book
    By macrorookie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-24-2014, 06:48 PM
  2. [SOLVED] paste source filepath information to destination book (code included)
    By macrorookie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-24-2014, 03:15 PM
  3. update and add rows to a destination table from my source table
    By mido609 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-05-2014, 05:59 PM
  4. [SOLVED] How to update destination file with source files closed?
    By Alex Costache in forum Excel General
    Replies: 2
    Last Post: 08-01-2006, 05:10 AM
  5. copying and pasting from source sheet to destination sheet without naming source sht?
    By Simon Lloyd in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-02-2006, 01:15 PM

Tags for this Thread

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