Add address based on text from drop down menu.

[Geek Girl 007]

Sheeela's Accounts.xlsx
	A	B
4	Invoice information
5
6	Invoice Number:
7	To:
8	Address:
9
10
11
12	Function:
13	Date:
14	Duration:
15	Cost:
INVOICE

Cells with Data Validation
Cell	Allow	Criteria
B7	List	=TABLES!$A$2:$A$5

If I select 'The Grapes' from cell B7 I want the address 12 Dow Street, SW12 3TH to appear in B8 however If I select 'Shaftesbury Club' I want the address 1 Peter Close, SE11 3GH to appear, and so forth.

Can this be done??

 

[mumps]

In order to do what you want, you would need a list (perhaps on a different sheet) that would include the drop down values in column A and the corresponding addresses in column B. Do you have this data anywhere in your workbook?

 

[Geek Girl 007]

In order to do what you want, you would need a list (perhaps on a different sheet) that would include the drop down values in column A and the corresponding addresses in column B. Do you have this data anywhere in your workbook?

Yes, I do now, how do I show you?

 

[Geek Girl 007]

Yes, I do now, how do I show you?

Sheeela's Accounts.xlsx
	A	B
1	CUSTOMERS	Address
2	Hurlingham Yacht Club	43a Deodar Road, London, SW15 2NP
3	Shaftesbury Club	128 Lavender Hill, London, SW11 5RB
4	The Grapes	39, Fairfield Street, London, SW18 1DX
5
6
7
8
9
10
MAIN

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your INVOICE sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in the drop down in B7.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B7")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim customer As Range
    Set customer = Sheets("MAIN").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not customer Is Nothing Then
        Range("B8") = customer.Offset(, 1)
    End If
    Application.ScreenUpdating = True
End Sub

 

[Geek Girl 007]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your INVOICE sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in the drop down in B7.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B7")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim customer As Range
    Set customer = Sheets("MAIN").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not customer Is Nothing Then
        Range("B8") = customer.Offset(, 1)
    End If
    Application.ScreenUpdating = True
End Sub

Is there any way it can be done without a macro?
It's just an accounts program for my mum, I'm trying to keep it simple.... thanks

 

[mumps]

If we put a formula in B8, it would return the correct address based on the selection in B7. However, this would work only the first time a selection is made in B7 because the address returned by the formula in B8 would overwrite the formula thereby deleting it. The macro is the simplest way to do what you want. It is entered as I described only once. Your mum doesn't even need to know it is there. All she has to do is enter the necessary data in the Main sheet. She can modify it by adding, deleting or changing the data without affecting the macro. After inserting the macro, save the file as a macro-enabled file. Macros of course, must be enabled in Excel.

 

[Geek Girl 007]

If we put a formula in B8, it would return the correct address based on the selection in B7. However, this would work only the first time a selection is made in B7 because the address returned by the formula in B8 would overwrite the formula thereby deleting it. The macro is the simplest way to do what you want. It is entered as I described only once. Your mum doesn't even need to know it is there. All she has to do is enter the necessary data in the Main sheet. She can modify it by adding, deleting or changing the data without affecting the macro. After inserting the macro, save the file as a macro-enabled file. Macros of course, must be enabled in Excel.

 

[Geek Girl 007]

Thank you so much, I'm gonna try with the formula. Thanks

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2
3
4	Invoice information
5
6	Invoice Number:
7	To:	Shaftesbury Club
8	Address:	128 Lavender Hill, London, SW11 5RB
9
10
11
12	Function:
13	Date:
14	Duration:
15	Cost:
Sheet5

Cell Formulas
Range	Formula
B8	B8	=VLOOKUP(B7,Main!A2:B4,2,0)

Cells with Data Validation
Cell	Allow	Criteria
B7	List	=Main!$A$2:$A$4