Adding New Data

[werdna]

As I have explained - I have a list of individuals like this...
[SURNAME] [FORENAME] [SEX] [GROUP]

[] denotes a cell.

This is all on one sheet.

On another sheet I have further details of the clients and thi
information (on the first sheet called 'list') is repeated usin
this....
='List'!A2 etc...
In the cells.

I did this so if that data changes, so does the rest of it. I also di
some ones for empty cells in case more was added. I've also named th
range on the first sheet.

Now, when I add someone new using a macro, I have to insert a new ro
at the end, or everytime I add using the macro if will just overwrit
the row I used when recording the macro.

But, when I do this it starts going wrong.

On row 70, there is an empty space. But on my second sheet I still hav
='List'!A70 for adding more. Upon running my macro, row 70 become
filled with data on my main sheet. However, as I inserted a row, row 7
on the linked sheet as changed so become ='List'!A71 and I dont kno
why!

I'm running Excel 97 because the computer I'm on isnt great. But I hav
XP 2003 on the other one I will use.

Any ideas

 

[medicenpringles]

ok, so...

First of all, define the name of your range on 'List' as this:

=OFFSET(Data!$A$1:$E$25,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

then, create a new worksheet, select the number of columns/rows tha
have data in them on 'List', and enter this in the first cell (assumin
your range is named "Stuff"):

='List'!Stuff

then, as you enter new data into the List worksheet, the range wil
automatically expand according to how much data you enter, and any tim
you want to expand the amount of data from 'List' you want to show
simply select the entire area, then click-drag the little corner-bo
out to that size.
*Note:* you may have to drag more than once: once for rows, once fo
columns. don't ask why you can't do both in one felled swoop.

hope that's what you need. play with it some. you'll find you can d
lots of cool stuff with this

 

[Max]

='List'!A2 etc...

One way to fix the "link" to always
point at the cells in "List"
is to use INDIRECT()

In say, Sheet2
--------------
Instead of having in say, B2: = 'List'!A2,
which is copied down the col

Try in B2: =INDIRECT("'List'!A"&ROW(A2))
Copy B2 down

Now, insertions of new rows in "List" will not affect
the "link" results returned in Sheet2