Excel Address Book

K

Kamal Subhani

Board Regular
Joined
Jan 26, 2004
Messages
146
Can any body help me make an address book in Excel

Suppose in Sheet 1 I have the Raw Data

with column headings

Name , Address , Station, Work Phone, Home Phone Mobile number

How can I make an address book in Excel

Suppose in the AddressBook Sheet

in A1 if we write the letter A the names starting with A will be displayed in Cell A2 Downwards along with the retreived information abt that recoreds Similarly In G1 if we write B then the recored starting with letter B should be retreived from the sheet 1 containing raw data in cell G2 downwards and this goes on in column M1=C S= D and so on

Plz help
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
HI!
take a look at this!

source sheet
Book1
ABCDE
1NameAddressStationWorkPhoneHomePhoneMobilenumber
2alanad15123568-9864
3aladinadd23124568-9865
4bartadd343125568-9866
5bartolomeadd48126568-9867
6daryladd56127568-9868
7cathyadd61128568-9869
8carenadd79129568-9870
Sheet2



Interface sheet. type in A1 the starting letter
Book1
ABCDE
1BA
2NameAddressStationWorkPhoneHomePhoneMobilenumber
3bartadd343125568-9866
4bartolomeadd48126568-9867
Sheet4


the code on sheet4<font face=Courier New><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target<SPAN style="color:#00007F">As</SPAN> Range)<SPAN style="color:#00007F">On</SPAN><SPAN style="color:#00007F">Error</SPAN><SPAN style="color:#00007F">GoTo</SPAN> en<SPAN style="color:#00007F">If</SPAN> Target = Range("A1")<SPAN style="color:#00007F">Then</SPAN>
Application.EnableEvents =<SPAN style="color:#00007F">False</SPAN>
    CRIT = "=" & Worksheets("SHEET4").Range("A1") & "*"
    Range("A3:E" & Range("A65536").End(xlUp).Row).ClearContents
    Worksheets("SHEET2").Range("A1").AutoFilter Field:=1, Criteria1:=CRIT, Operator:=xlAnd
    Worksheets("SHEET2").Range("A1:E" & Worksheets("SHEET2").Range("A65536").End(xlUp).Row).Copy Destination:=Worksheets("SHEET4").Range("A2")
    Worksheets("SHEET2").Range("A1").AutoFilter<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
en:
Application.EnableEvents =<SPAN style="color:#00007F">True</SPAN>
Worksheets("SHEET4").Range("A1").Select<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
That's really cool!! Will come in very handy, but is there a way to have you select a cell that already has a letter in it and have the code run? For instance, Just using names in Sheet2...Last name in column A, First in column B... on Sheet1.... instead of typing a letter in A1 and have it find the names,...can I choose a letter in column E3:F13..which is A-Z...and have the code look for the letter I've chosen? Am I too confusing??

Thanks for any help or ideas..

Dave
 
Upvote 0
Davers said:
That's really cool!! Will come in very handy, but is there a way to have you select a cell that already has a letter in it and have the code run? For instance, Just using names in Sheet2...Last name in column A, First in column B... on Sheet1.... instead of typing a letter in A1 and have it find the names,...can I choose a letter in column E3:F13..which is A-Z...and have the code look for the letter I've chosen? Am I too confusing??

Thanks for any help or ideas..

Dave
Click to expand...

Hi!
ok try this one!

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> en
TMP = Target.Address
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Set</SPAN> ISECT = Application.Intersect(Target, Worksheets("SHEET1").Range("E3:F13"))
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> ISECT <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    CRIT = "=" & Target & "*"
    Worksheets("SHEET1").Range("A3:E" & Range("A65536").End(xlUp).Row).ClearContents
    Worksheets("SHEET2").Range("A1").AutoFilter Field:=1, Criteria1:=CRIT, Operator:=xlAnd
    Worksheets("SHEET2").Range("A1:E" & Worksheets("SHEET2").Range("A65536").End(xlUp).Row).Copy Destination:=Worksheets("SHEET1").Range("A2")
    Worksheets("SHEET2").Range("A1").AutoFilter
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
en:
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
Worksheets("SHEET1").Range(TMP).Select
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 
Upvote 0
Works like a charm!!! Thanks for taking the time out to help!!

Have a good morning,

Dave o_O
 
Upvote 0
Thanx Sixth sense for the reply.

Well can this be done with the help of the formula instead of a VBA Code
which will be much easier for me ( I need the formula Approach)

Thanks and Regards

Kamal
 
Upvote 0
Book1
ABCDEF
1NameAddressOfficeMobileFaxResidence
2AndrewW.Melsh123-ParkLaneStreet123456123-0125841547892112233
3CarryHolmes234-Jeffereylanes234568235-12121215718558223344
4ScottWilliams568-DHATown214581123-01258655716580334455
5BeckhamD.Swarz12-Libertylanes987512123-01258455710580445566
6CarlDesouza15-ahmadBlock547821123-01258216987521556677
Main List



I want to make an address book say 26 Sheets Named A, B, C, D ..... Y, Z

The Raw data will be entered like the sheet shown above and the Dat is automatically sorted alphabetically into the 26 Sheets

Plz Help

Kamal
 
Upvote 0
Sorry, I have no idea how to do the same thing with formulae...Hopefully one of the guru's has an idea! :rolleyes: I'll keep looking around though to see if I can find anything...

Good luck

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Excel on Mac - file repair issues
Replies
1
Views
1K
dskills
D
welshgasman
  • Question
Mailmerge from Excel sheet substituting phone number for 0
Replies
8
Views
468
welshgasman
welshgasman
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
S
  • Locked
  • Question
combining macros to be tie to a single button.
Replies
1
Views
364
Fluff
Fluff
C
  • Question
VBA Copy, Paste, Match query
Replies
3
Views
575
ckdragon
C

Forum statistics

Threads
1,214,518
Messages
6,119,988
Members
448,935
Latest member
ijat

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top