Picking up the names that is not required using macros or formulas

**share knowledge** · 07-05-2009, 02:42 PM

Hello

I am herewith 2 questions..

Number 1: I have a list of approved names which i may see everyday or sometimes I will not see any of these in my spreadsheet. But If someday any new name comes could this be identified by macros?

Approved names are (for example I am giving the cartoon names)
Tom
Jerry
Mickey
Bravo
Donald
Spider

So If I see other than these names i want the entire column to be picked up in the workbook named Unapproved - Sheet 1. And these names will always appear in Column I.

My second question is very very simple.. I couldn't do it..

I have kept two workbooks open assuming book 1 and book 2. Can i have a macro where if i keep my cursor in the Book 2 i should get the name of the book 1 in the column D.

Please note Book 1 can be named everytime differently.

Thanks a mill for your help.

**Leith Ross** · 07-05-2009, 05:11 PM

Hello share knowledge,

It would help to know the names of the workbooks. Is workbook 2 named "Unapproved"? What is a typical name for workbook 1?

**protonLeah** · 07-05-2009, 05:27 PM

Since you did not provide specific descriptions of your workbook, here is a general possibility for question #1:
Sheet1, column A is Titled Approved Names, with a list of names down the column. Then define a named range "Approved_Names" and in the refers to box enter:

Please Login or Register  to view this content.

To use it in a macro (maybe some loop):

Please Login or Register  to view this content.

**share knowledge** · 07-06-2009, 01:12 AM

Thanks Both of you!

The workbook1 name is different everytime. Otherwise I can say I have two workbooks kept open. You can see these names in one workbook and the other workbook does not have anything (Workbook 2 has an empty sheet named unapproved). So the code should find these names and if anything is not in the list it should copy that entire column and pick it up in the unapproved sheet in workbook 2.

Everytime the name of the workbook is different. It will be great if you can provide me the code that is not workbook specific.

If you have a code only with workbook specific. no problem please provide it.. I ll edit when i run a macro. (In tat case keep the names of the workbooks as Book1 and Book2
Book 1 is the place where you will see the names and Book 2 is the place where the unapproved names has to get picked up).

Thanks a mill.

**share knowledge** · 07-06-2009, 04:39 AM

Hello Experts - Any solutions on my first and second question.

**ravishankar** · 07-06-2009, 05:24 AM

HI
pl attach a sample workbook and expected output.
Ravi

**share knowledge** · 07-07-2009, 06:10 PM

Will this help?

I think this can be done using any inbuilt formulas.

Also i tried doing the below, but getting the error "Name"
=OFFSET(Sheet1!$A$1,1,0,MATCH("*",Sheet1$A:$A,-1)-1,1)