auto update from on sheet to another - link?

[lhradowy]

have a worksheet that has two columns
NAME and either a 1 or blank.

Meaning I am making a wedding list, with names and 1 if they are coming and blank if not.

I want to take the names that have a 1 in the number column and copy it to the guest list worksheet.
I have tried Advanced filter, but if I add a name to my list, it does not copy it to the next worksheet.

How do I set this up so it I add another name to the range it will automatically be added to the guest spreadsheet?

 

[venkat1926]

you need a dynamic range name

suppose you have data like this

<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" span="2" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" width="64" height="17">name</td> <td style="width:48pt" width="64">attending</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">a</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">s</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">d</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">f</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">g</td> <td align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">h</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">j</td> <td align="right">1</td> </tr> </tbody></table>

click insert(menu bar)-name-define
at the top under "names in workbook"
type some name in one word e.g. attendance
now in the bottom "refer to"
copy paste this formula

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)

clcik OK

to test this

hit control+G and type under reference at the bottom
attendance
take care of spelling
click ok

now the data will be highlilghted

add two or three rows.
and again control+G etc
see what is selected.
(even if you delete some rows it will work)
you can use this range name for your filter.

 

[venkat1926]

sorry some problem in uploading reply. it uploaded twice. removed the message here