Adding rows of data to 2 worksheets that are linked with formulas to eachother...

  • Thread starter Thread starter Angela
  • Start date Start date
A

Angela

I have 2 worksheets. Sheet 1 contains address information for names
of people we contact (including the names). Sheet 2 contains some of
the contact information, including about 15 columns of dates that
aren't included in sheet 1. I need to add in about 100 rows of new
data that isn't included on either sheet.

My problem is that the data that is the same on sheet 2 is linked to
the cells in sheet 1. Rows in sheet2 read
(=Sheet1!A2)(=Sheet1!B2)(=Sheet1!C2)(=Sheet1!G2),etc (they skip some
colmns from Sheet 1) for every row.

How do I add the extra 100 rows in without mixing up the data in my
initial rows? I also need to be able to alphabetize them by sorting.
When I do this I get 2 copies of the new data (b/c the new data copies
over the old) Does this make sense? I need the date part of sheet2
to line up with the formulas and they don't. Anyone have any ideas?
Thanks in advance.
 
It appears you may have caused yourself some problems by having data
entered on 2 sheets instead of using one for all data and the other as
an output sheet.

If all data was entered to one sheet with the other(s) "reading" the
cells, inserting would be very easy, just highlight the rows to be
inserted right click the mouse and choose "Insert", the just copy the
formula on the other sheet to cover all the rows.

Sorting would also be easier as only one sheet needs to be sorted, with
the other reading the new values in A1, A2 etc.

If you want to keep you format you can still do it in a similar way.

1. Select all relevant sheets by holding the Ctrl key whilst clicking
the sheet tab.

2. Highlight and insert cells as above (this time the corresponding
rows will be added to all highlighted sheets)

3. Deselct the sheets by clinking on the current sheet tab again
holding Ctrl when clicking.

4. copy all formulae on sheet 2 to the new rows.

5.enter your data on each sheet as normal.

The sorting is a bit more complex, if you sort sheet1 first the linked
data in the cells on sheet2 also appears to have been sorted, but the
entered items on sheet2 are now against the wrong items from sheet1.

I will add the sorting part after I have tested it.

Hope this helps.

Mike
 
Thanks Mike. It's the sorting part that's the really tough part. I
appreciate the short cut of pasting the data to both places at once.
If you figure it out, please post again!
 
Back
Top