+ Reply to Thread
Results 1 to 20 of 20

Linking adjacent cells to each other

  1. #1
    Registered User
    Join Date
    06-16-2008
    Location
    Arlington, VA
    MS-Off Ver
    2007
    Posts
    93

    Linking adjacent cells to each other

    I need to have three rows link to each other. The first column cell A is a paste link and is always changing. The second B is a vlookup referenceing A and finding it in a changing table...the source for the paste link. C needs to follow the other two cells and remain "attached" to it. However, C has to be a cell that is hardcoded somewhere, meaning that C is not found in the source data and must be determined by a person. I have tried to link the sheets through vlookups and other formulas, however, this only works when the data is sorted and nothing new is added. I need a static sheet that can take in new values. Any ideas would be extremely helpful. I need to create some sort of relationship between the cells.

    Thanks
    Last edited by nms2130; 06-26-2008 at 12:59 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    Can you upload a short example of what you have and what you need? VLookup can be made to work without sorting and ranges can be made dynamic but I need to understand it better before I can come up with a solution.

    ChemistB

  3. #3
    Registered User
    Join Date
    06-16-2008
    Location
    Arlington, VA
    MS-Off Ver
    2007
    Posts
    93

    sample data

    Here is some sample data that is fairly representative of the data I will be using.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    Okay, some more questions.

    1. Is column A just a direct formula (e.g. =OtherBook!A6)?

    2. Is "Alternate Table" the same as data as above or a different list?

    To get your alternate table to be dynamic as you add numbers, set it up as a defined name (Insert > Name) (e.g. Alt_Table, no blanks)
    with the formula;
    Please Login or Register  to view this content.
    Questions

    ChemistB

  5. #5
    Registered User
    Join Date
    06-16-2008
    Location
    Arlington, VA
    MS-Off Ver
    2007
    Posts
    93

    additional questions

    1. Is column A just a direct formula (e.g. =OtherBook!A6)? YES

    2. Is "Alternate Table" the same as data as above or a different list? SAME DATA

    The alternate table is already named. You suggest using OFFSET instead of a paste link to acquire the information in nms2130sample.xls ? For columns A & B ?

    Everyday, I copy data from one source and paste it into a workbook. Then link other workbooks to it in order to manipulate the data. The source cannot be linked.

  6. #6
    Registered User
    Join Date
    06-16-2008
    Location
    Arlington, VA
    MS-Off Ver
    2007
    Posts
    93

    Missunderstanding

    Do you mean to set the range of the named table equal to the OFFSET formula?

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    Okay, so, (pardon my denseness but it's Thursday) you copy two columns of data from an external source and this is your masterworkbook.

    Other workbooks are linked to this table. Are the notes in the other workbooks or would they be added to the master workbook?

    I'll work up a quick example of using the dynamic table for you.

    ChemistB

  8. #8
    Registered User
    Join Date
    06-16-2008
    Location
    Arlington, VA
    MS-Off Ver
    2007
    Posts
    93

    Notes

    The notes are added to individual workbooks. For example, someone will add notes to Names that start with A-D another will add notes to E-O in another workbook.

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    I created 2 workbooks, the master one that you paste in from an outside source. This one contains the dynamic table.
    And the linked one containing the VLOOKUP. Take a look and see if I'm heading in the right direction.

    ChemistB
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    Did you want those individual notes linked back to the master workbook?

    Are Nick, Oscar and Mary the people who have these individual workbooks?

    ChemistB

  11. #11
    Registered User
    Join Date
    06-16-2008
    Location
    Arlington, VA
    MS-Off Ver
    2007
    Posts
    93

    notes in notebooks

    No, the names are not the names of the users. They are part of the data. Nick belongs to that number etc.

  12. #12
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    But you do want the notes from the individual workbooks to appear in the master workbook?

    ChemistB

  13. #13
    Registered User
    Join Date
    06-16-2008
    Location
    Arlington, VA
    MS-Off Ver
    2007
    Posts
    93

    Mastertable

    You had to type in the phone number though, right? There is no way of knowing when new numbers and names come in. I would like those to remain dynamic links. The way I see it, I need to have the master workbook include the names and numbers. These go to another workbook. This workbook contains has notes inputed into it. Other notebooks feed from that notebook where they will repeat a similar process. So for 3 users I foresee, the masternotebook which will only be opnened to paste the new data, the second notebook where notes will be added to one column daily, then closed, and 3 other notebooks that will be opened by the users. Maybe this sounds like too much, I don't know.

  14. #14
    Registered User
    Join Date
    06-16-2008
    Location
    Arlington, VA
    MS-Off Ver
    2007
    Posts
    93

    notes

    No, I do not want them to appear in the master workbook but I do want them to appear on the users' work books.

  15. #15
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    Okay, more questions and a summation.

    The masterworkbook is pasted in from an outside source and contains 2 columns of data.

    From this there is a secondary workbook which contains notes which is linked but otherwise is a duplicate of the masterworkbook.

    Then there are 3 (or more) workbooks that are linked to this secondary workbook and people use on a regular basis and enter notes into.

    1. How are the tertiary workbooks different? Does each individual have certain numbers they work on? Is it an exact copy of the secondary workbook except they add notes? If it's not, how do the phone numbers get put into the tertiary workbooks?

    2. Are notes manually copied from the tertiary workbooks into the secondary workbook on a regular basis?

    I'll figure this out eventually.

    ChemistB

  16. #16
    Registered User
    Join Date
    06-16-2008
    Location
    Arlington, VA
    MS-Off Ver
    2007
    Posts
    93

    Workbooks

    1. How are the tertiary workbooks different?
    They are relatively similar to the masterbook, however, the values are transposed using an OFFEST function. The numbers at top with the name and the notes under.
    Does each individual have certain numbers they work on?
    They each work on certain names. I have this part solved using
    IF(ROWS($A$1:$A1)>COUNTIF(Main!$B$1:$B$100,BETA!$A$13),"",INDEX(Main!$A$1:$A$100,SMALL(IF(Main!$B$1:$B$100=BETA!$A$13,ROW(Main!$B$1:$B$100)-ROW(Main!$B$1)+1),ROWS($A$1:$A1)))) which returns a list of all names starting with A, B, C etc.

    Is it an exact copy of the secondary workbook except they add notes? If it's not, how do the phone numbers get put into the tertiary workbooks?
    Right now they are fed through links.

    2. Are notes manually copied from the tertiary workbooks into the secondary workbook on a regular basis?
    Right now no. Right now new numbers, names and notes are cpoied in cell by cell.

    What is the difference between using the Dynamic table and using paste links? The 2 main things that I do not want to have to do are manually type in or copy and paste the names or numbers. From there the notes just need to form a relationship with the names and numbers.

  17. #17
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    I am attaching what I think your different workbooks look like. I combined them all into 1 workbook (each tab represents a different workbook). Take a look and make changes depending on reality. Show me where you have formulas also. I also back linked the notes from the tertiary to the secondary on the assumption that person 1 has Nick and Ned, 2 has Matt and Mary and 3 has the rest. This one doesn't have the dynamic tables set up yet.

    I do not see the need for the secondary workbook if you can paste your new data into the first 2 columns of Main without erasing the other columns. Is there a reason?

    ChemistB
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    06-16-2008
    Location
    Arlington, VA
    MS-Off Ver
    2007
    Posts
    93

    sample

    I have attached a sample with some formulas in it. There is much more to the sheet of course. What I have works great for everything except for linking the notes to the number.

    I can't upload the data because it is too big for the site.

  19. #19
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    Did you try zipping the file? (or just reduce it moreso)

    ChemistB

  20. #20
    Registered User
    Join Date
    06-16-2008
    Location
    Arlington, VA
    MS-Off Ver
    2007
    Posts
    93

    Zipping

    Sorry I don't know how to do that on my archaic office computer. I can try it later from home. Thanks for your help and hanging in there.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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