Linking adjacent cells to each other

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

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

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

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.

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

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

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.

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

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

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

But you do want the notes from the individual workbooks to appear in the master workbook?

ChemistB

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.

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

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

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.

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

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.

Did you try zipping the file? (or just reduce it moreso)

ChemistB

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.