IF function problem with drop down menus

M

mj_bowen

Board Regular
Joined
Oct 4, 2009
Messages
101
Hi,
I have attached a link to an example of the Excel worksheet!
http://www.box.net/shared/x1gxd00lpn
I would like to know which formula to add so that when using the drop-down choices in the
‘APP Group’ tab…
1. When I select for example, ‘Sam’ in cell B3 then the value next to it in cell 3c appears next to the name ‘Sam’ in D3 in the APP Sheets tab.
2. If another name is selected in cell B3, eg, Mark, then the value next to it in cell 3c appears next to the name ‘Mark’ in D11 in the APP Sheets tab.
3. If another name is selected in cell B3, eg, Bernie, then the value next to it in cell 3c appears next to the name ‘Bernie’ in D15 in the APP Sheets tab.
4. If another name is selected in cell B3, eg, Fred, then the value next to it in cell 3c appears next to the name ‘Fred’ in D7 in the APP Sheets tab.
5. The same rule would also apply to the other drop down cells on the APP Group tab - B5, E3 and E5.
6. If the same name is selected in two drop down cells then an ‘error’ message appears!
I have tried using the IF function but feel it may be more complicated than that!

Yours hopefully,
Matt
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
go to your APP Groups Tab

Right click on Tab and go to view Code

Copy and paste the following:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B3")) Is Nothing Then
        If Range("B3").Value = "Sam" Then
            Worksheets("APP Sheets").Range("D3").Value = Worksheets("APP Groups").Range("C3").Value
        End If
    End If
    If Not Intersect(Target, Range("B5")) Is Nothing Then
        If Range("B5").Value = "Mark" Then
            Worksheets("APP Sheets").Range("D7").Value = Worksheets("APP Groups").Range("C5").Value
        End If
    End If
    If Not Intersect(Target, Range("E3")) Is Nothing Then
        If Range("E3").Value = "Fred" Then
            Worksheets("APP Sheets").Range("D11").Value = Worksheets("APP Groups").Range("F3").Value
        End If
    End If
    If Not Intersect(Target, Range("E5")) Is Nothing Then
        If Range("E5").Value = "Bernie" Then
            Worksheets("APP Sheets").Range("D15").Value = Worksheets("APP Groups").Range("F5").Value
        End If
    End If
    
End Sub


Should work :D
 
Upvote 0
Wow! Thank you for your help..it seems to be almost working...I probably didn't explain it very well! Sorry!

Is it possible so that which ever drop down cell is being used in the APP groups tab, ie, B3, B5, E3 or E5 the value of the cell next to it is displayed next to the corresponding name on the APP sheets tab ,

For example, if on one day I selected B3 in the APP Group tab to be 'Mark' then the cell next to him (c3) would be displayed in cell D11 on the APP sheet tab. Then the next day maybe i chose Mark in cell E5 so the value of F5 would be displayed next to Mark in D11 on the APP sheet tab. The day after i may chose Mark from B5 or so on.

Basically, I would like to be able to chose all four names at different times independently of one another and the value next to the name would be displayed next to the relevent name on the APP sheets tab!

I hope this makes sense! Thank you once again for your help!

Regards, Matt

P.s. I have attached the file with your code in!
http://www.box.net/shared/hxr0isxpfi
 
Upvote 0
ah ok

changed :)

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("names_range")) Is Nothing Then
        For Each Cell In Range("names_range")
        If Cell.Value = "Sam" Then
            Worksheets("APP Sheets").Range("D3").Value = Cell.Offset(0, 1).Value
        End If
        If Cell.Value = "Mark" Then
            Worksheets("APP Sheets").Range("D11").Value = Cell.Offset(0, 1).Value
        End If
        If Cell.Value = "Fred" Then
            Worksheets("APP Sheets").Range("D7").Value = Cell.Offset(0, 1).Value
        End If
        If Cell.Value = "Bernie" Then
            Worksheets("APP Sheets").Range("D15").Value = Cell.Offset(0, 1).Value
        End If
        Next Cell
    End If
    
End Sub

In this code you got to select B3, B5, E3 and E5 and group them and give them a range name of "names_range" for the code to work.

hopefully it make sense :) I will run through it if you require. cheers
 
Upvote 0
Hi, thanks for the code again...I've tried entering "names_range" as a group but am not sure if I'm doing it right!!

I was thinking again today if I'd expained it clearly enough . . it was rather late last night when i posted!

Basically if any of the names is selected in any of the drop down cells, then the score that is next to that cell eg, 1a,2b, 3c, 4c is shown next to the appropriate name in the APP sheets tab!

Thanks, Matt
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Drop Down Box & Name Manager
Replies
2
Views
289
Peter_SSs
Peter_SSs
M
  • Question
How to track choices in a workbook from several drop down lists and reflect the running choices throughout the month
Replies
0
Views
131
mellojeff
M
S
  • Solved
Worksheet Change Event
Replies
3
Views
161
sspatriots
S
T
  • Solved
Nesting in the FILTER function
Replies
4
Views
413
Eric W
Eric W
S
  • Solved
Dynamic Sheet using Drop Down List & INDIRECT using External File
Replies
5
Views
433
smozgur
smozgur

Forum statistics

Threads
1,216,127
Messages
6,129,022
Members
449,481
Latest member
joaotcosta23

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