Select name from drop down and return associated value

Hi
I have a spreadsheet where I need to return a value which is an
alphanumerical code but the users know only the name.

Example:

In worksheet A there are details of clients.  If the clients
participate in a course we are required to record the course code not
the name.

Column C is where I want the value to appear.  What i would like to
occur is that the user selects from a drop down list the name of the
course but on selection the value returned is the course code.

Is this possible?

0
dverras (2)
9/30/2009 11:49:12 PM
excel 39879 articles. 2 followers. Follow

6 Replies
726 Views

Similar Articles

[PageSpeed] 8

Debbie
    Are you saying that you want the user to select from a dropdown some 
course name, but you want Excel to display the course code in that same cell 
and not the course name selected?  If so, yes, this can be done with VBA 
(programming).  I take it that your workbook has a list of course names and 
matching course codes?
The following macro will do that.  I assumed that your list of course names 
is in Column A of a sheet named "List" starting in A2 and the corresponding 
course codes are in Column B of the same sheet.  Note that your setup of the 
Data Validation cell must include to not display an error when a "wrong" 
entry is made.  Note that this macro must be placed in the sheet module of 
the sheet that contains the Data Validation cell.  Right-click on that sheet 
tab and select View Code.  Paste this macro into that module.  "X" out of 
the module to return to your sheet.  HTH  Otto
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If IsEmpty(Target.Value) Then Exit Sub
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        With Sheets("List")
            Application.EnableEvents = False
            Range("A1") = .Range("CourseNames"). _
                Find(What:=Target, LookAt:=xlWhole).Offset(, 1)
            Application.EnableEvents = True
        End With
    End If
End Sub
"DebbieV" <dverras@westnet.com.au> wrote in message 
news:8caf5d40-a6c6-4b2a-94a8-f5e698628dba@r24g2000prf.googlegroups.com...
> Hi
> I have a spreadsheet where I need to return a value which is an
> alphanumerical code but the users know only the name.
>
> Example:
>
> In worksheet A there are details of clients.  If the clients
> participate in a course we are required to record the course code not
> the name.
>
> Column C is where I want the value to appear.  What i would like to
> occur is that the user selects from a drop down list the name of the
> course but on selection the value returned is the course code.
>
> Is this possible?
> 


0
10/1/2009 1:36:11 AM
Only slightly tighter
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("b1").Address _
Or Target.Count > 1 Then Exit Sub

Application.EnableEvents = False
   Target = Application.VLookup(Target, [coursenames], 2, 0)
   'OR
   'Target = Range("CourseNames"). _
   'Find(Target, LookAt:=xlWhole).Offset(, 1)
Application.EnableEvents = True
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Otto Moehrbach" <moehrbachoextra@bellsouth.net> wrote in message 
news:%23RikOejQKHA.4580@TK2MSFTNGP06.phx.gbl...
> Debbie
>    Are you saying that you want the user to select from a dropdown some 
> course name, but you want Excel to display the course code in that same 
> cell and not the course name selected?  If so, yes, this can be done with 
> VBA (programming).  I take it that your workbook has a list of course 
> names and matching course codes?
> The following macro will do that.  I assumed that your list of course 
> names is in Column A of a sheet named "List" starting in A2 and the 
> corresponding course codes are in Column B of the same sheet.  Note that 
> your setup of the Data Validation cell must include to not display an 
> error when a "wrong" entry is made.  Note that this macro must be placed 
> in the sheet module of the sheet that contains the Data Validation cell. 
> Right-click on that sheet tab and select View Code.  Paste this macro into 
> that module.  "X" out of the module to return to your sheet.  HTH  Otto
> Private Sub Worksheet_Change(ByVal Target As Range)
>    If Target.Count > 1 Then Exit Sub
>    If IsEmpty(Target.Value) Then Exit Sub
>    If Not Intersect(Target, Range("A1")) Is Nothing Then
>        With Sheets("List")
>            Application.EnableEvents = False
>            Range("A1") = .Range("CourseNames"). _
>                Find(What:=Target, LookAt:=xlWhole).Offset(, 1)
>            Application.EnableEvents = True
>        End With
>    End If
> End Sub
> "DebbieV" <dverras@westnet.com.au> wrote in message 
> news:8caf5d40-a6c6-4b2a-94a8-f5e698628dba@r24g2000prf.googlegroups.com...
>> Hi
>> I have a spreadsheet where I need to return a value which is an
>> alphanumerical code but the users know only the name.
>>
>> Example:
>>
>> In worksheet A there are details of clients.  If the clients
>> participate in a course we are required to record the course code not
>> the name.
>>
>> Column C is where I want the value to appear.  What i would like to
>> occur is that the user selects from a drop down list the name of the
>> course but on selection the value returned is the course code.
>>
>> Is this possible?
>>
>
> 

0
dguillett1 (2487)
10/1/2009 1:23:48 PM
Thanks Don.  It's always good to see another view of it.  Otto
"Don Guillett" <dguillett1@austin.rr.com> wrote in message 
news:ua6ErppQKHA.2092@TK2MSFTNGP04.phx.gbl...
> Only slightly tighter
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address <> Range("b1").Address _
> Or Target.Count > 1 Then Exit Sub
>
> Application.EnableEvents = False
>   Target = Application.VLookup(Target, [coursenames], 2, 0)
>   'OR
>   'Target = Range("CourseNames"). _
>   'Find(Target, LookAt:=xlWhole).Offset(, 1)
> Application.EnableEvents = True
> End Sub
>
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "Otto Moehrbach" <moehrbachoextra@bellsouth.net> wrote in message 
> news:%23RikOejQKHA.4580@TK2MSFTNGP06.phx.gbl...
>> Debbie
>>    Are you saying that you want the user to select from a dropdown some 
>> course name, but you want Excel to display the course code in that same 
>> cell and not the course name selected?  If so, yes, this can be done with 
>> VBA (programming).  I take it that your workbook has a list of course 
>> names and matching course codes?
>> The following macro will do that.  I assumed that your list of course 
>> names is in Column A of a sheet named "List" starting in A2 and the 
>> corresponding course codes are in Column B of the same sheet.  Note that 
>> your setup of the Data Validation cell must include to not display an 
>> error when a "wrong" entry is made.  Note that this macro must be placed 
>> in the sheet module of the sheet that contains the Data Validation cell. 
>> Right-click on that sheet tab and select View Code.  Paste this macro 
>> into that module.  "X" out of the module to return to your sheet.  HTH 
>> Otto
>> Private Sub Worksheet_Change(ByVal Target As Range)
>>    If Target.Count > 1 Then Exit Sub
>>    If IsEmpty(Target.Value) Then Exit Sub
>>    If Not Intersect(Target, Range("A1")) Is Nothing Then
>>        With Sheets("List")
>>            Application.EnableEvents = False
>>            Range("A1") = .Range("CourseNames"). _
>>                Find(What:=Target, LookAt:=xlWhole).Offset(, 1)
>>            Application.EnableEvents = True
>>        End With
>>    End If
>> End Sub
>> "DebbieV" <dverras@westnet.com.au> wrote in message 
>> news:8caf5d40-a6c6-4b2a-94a8-f5e698628dba@r24g2000prf.googlegroups.com...
>>> Hi
>>> I have a spreadsheet where I need to return a value which is an
>>> alphanumerical code but the users know only the name.
>>>
>>> Example:
>>>
>>> In worksheet A there are details of clients.  If the clients
>>> participate in a course we are required to record the course code not
>>> the name.
>>>
>>> Column C is where I want the value to appear.  What i would like to
>>> occur is that the user selects from a drop down list the name of the
>>> course but on selection the value returned is the course code.
>>>
>>> Is this possible?
>>>
>>
>>
> 


0
10/1/2009 2:36:19 PM
On Oct 2, 1:36=A0am, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
wrote:
> Thanks Don. =A0It's always good to see another view of it. =A0Otto"Don Gu=
illett" <dguille...@austin.rr.com> wrote in message
>
> news:ua6ErppQKHA.2092@TK2MSFTNGP04.phx.gbl...
>
>
>
> > Only slightly tighter
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Address <> Range("b1").Address _
> > Or Target.Count > 1 Then Exit Sub
>
> > Application.EnableEvents =3D False
> > =A0 Target =3D Application.VLookup(Target, [coursenames], 2, 0)
> > =A0 'OR
> > =A0 'Target =3D Range("CourseNames"). _
> > =A0 'Find(Target, LookAt:=3DxlWhole).Offset(, 1)
> > Application.EnableEvents =3D True
> > End Sub
>
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > dguille...@austin.rr.com
> > "Otto Moehrbach" <moehrbachoex...@bellsouth.net> wrote in message
> >news:%23RikOejQKHA.4580@TK2MSFTNGP06.phx.gbl...
> >> Debbie
> >> =A0 =A0Are you saying that you want the user to select from a dropdown=
 some
> >> course name, but you want Excel to display the course code in that sam=
e
> >> cell and not the course name selected? =A0If so, yes, this can be done=
 with
> >> VBA (programming). =A0I take it that your workbook has a list of cours=
e
> >> names and matching course codes?
> >> The following macro will do that. =A0I assumed that your list of cours=
e
> >> names is in Column A of a sheet named "List" starting in A2 and the
> >> corresponding course codes are in Column B of the same sheet. =A0Note =
that
> >> your setup of the Data Validation cell must include to not display an
> >> error when a "wrong" entry is made. =A0Note that this macro must be pl=
aced
> >> in the sheet module of the sheet that contains the Data Validation cel=
l.
> >> Right-click on that sheet tab and select View Code. =A0Paste this macr=
o
> >> into that module. =A0"X" out of the module to return to your sheet. =
=A0HTH
> >> Otto
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> =A0 =A0If Target.Count > 1 Then Exit Sub
> >> =A0 =A0If IsEmpty(Target.Value) Then Exit Sub
> >> =A0 =A0If Not Intersect(Target, Range("A1")) Is Nothing Then
> >> =A0 =A0 =A0 =A0With Sheets("List")
> >> =A0 =A0 =A0 =A0 =A0 =A0Application.EnableEvents =3D False
> >> =A0 =A0 =A0 =A0 =A0 =A0Range("A1") =3D .Range("CourseNames"). _
> >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Find(What:=3DTarget, LookAt:=3DxlWhole)=
..Offset(, 1)
> >> =A0 =A0 =A0 =A0 =A0 =A0Application.EnableEvents =3D True
> >> =A0 =A0 =A0 =A0End With
> >> =A0 =A0End If
> >> End Sub
> >> "DebbieV" <dver...@westnet.com.au> wrote in message
> >>news:8caf5d40-a6c6-4b2a-94a8-f5e698628dba@r24g2000prf.googlegroups.com.=
...
> >>> Hi
> >>> I have a spreadsheet where I need to return a value which is an
> >>> alphanumerical code but the users know only the name.
>
> >>> Example:
>
> >>> In worksheet A there are details of clients. =A0If the clients
> >>> participate in a course we are required to record the course code not
> >>> the name.
>
> >>> Column C is where I want the value to appear. =A0What i would like to
> >>> occur is that the user selects from a drop down list the name of the
> >>> course but on selection the value returned is the course code.
>
> >>> Is this possible?- Hide quoted text -
>
> - Show quoted text -

Hi Otto & Don

Thanks for your responses.  I am quite the novice when it comes to
macros.  I have tried but to no success.  Is there any other way? I
think I may have to increase my knowledge if not.

cheers
Debbie
0
dverras (2)
10/4/2009 12:19:01 PM
      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You send a clear explanation of what you want
      3. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"DebbieV" <dverras@westnet.com.au> wrote in message 
news:c94dfc15-3db3-41d1-87a4-bb00c9da8ec8@u16g2000pru.googlegroups.com...
On Oct 2, 1:36 am, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
wrote:
> Thanks Don. It's always good to see another view of it. Otto"Don Guillett" 
> <dguille...@austin.rr.com> wrote in message
>
> news:ua6ErppQKHA.2092@TK2MSFTNGP04.phx.gbl...
>
>
>
> > Only slightly tighter
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Address <> Range("b1").Address _
> > Or Target.Count > 1 Then Exit Sub
>
> > Application.EnableEvents = False
> > Target = Application.VLookup(Target, [coursenames], 2, 0)
> > 'OR
> > 'Target = Range("CourseNames"). _
> > 'Find(Target, LookAt:=xlWhole).Offset(, 1)
> > Application.EnableEvents = True
> > End Sub
>
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > dguille...@austin.rr.com
> > "Otto Moehrbach" <moehrbachoex...@bellsouth.net> wrote in message
> >news:%23RikOejQKHA.4580@TK2MSFTNGP06.phx.gbl...
> >> Debbie
> >> Are you saying that you want the user to select from a dropdown some
> >> course name, but you want Excel to display the course code in that same
> >> cell and not the course name selected? If so, yes, this can be done 
> >> with
> >> VBA (programming). I take it that your workbook has a list of course
> >> names and matching course codes?
> >> The following macro will do that. I assumed that your list of course
> >> names is in Column A of a sheet named "List" starting in A2 and the
> >> corresponding course codes are in Column B of the same sheet. Note that
> >> your setup of the Data Validation cell must include to not display an
> >> error when a "wrong" entry is made. Note that this macro must be placed
> >> in the sheet module of the sheet that contains the Data Validation 
> >> cell.
> >> Right-click on that sheet tab and select View Code. Paste this macro
> >> into that module. "X" out of the module to return to your sheet. HTH
> >> Otto
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> If Target.Count > 1 Then Exit Sub
> >> If IsEmpty(Target.Value) Then Exit Sub
> >> If Not Intersect(Target, Range("A1")) Is Nothing Then
> >> With Sheets("List")
> >> Application.EnableEvents = False
> >> Range("A1") = .Range("CourseNames"). _
> >> Find(What:=Target, LookAt:=xlWhole).Offset(, 1)
> >> Application.EnableEvents = True
> >> End With
> >> End If
> >> End Sub
> >> "DebbieV" <dver...@westnet.com.au> wrote in message
> >>news:8caf5d40-a6c6-4b2a-94a8-f5e698628dba@r24g2000prf.googlegroups.com...
> >>> Hi
> >>> I have a spreadsheet where I need to return a value which is an
> >>> alphanumerical code but the users know only the name.
>
> >>> Example:
>
> >>> In worksheet A there are details of clients. If the clients
> >>> participate in a course we are required to record the course code not
> >>> the name.
>
> >>> Column C is where I want the value to appear. What i would like to
> >>> occur is that the user selects from a drop down list the name of the
> >>> course but on selection the value returned is the course code.
>
> >>> Is this possible?- Hide quoted text -
>
> - Show quoted text -

Hi Otto & Don

Thanks for your responses.  I am quite the novice when it comes to
macros.  I have tried but to no success.  Is there any other way? I
think I may have to increase my knowledge if not.

cheers
Debbie 

0
dguillett1 (2487)
10/4/2009 1:47:15 PM
Debbie
    Just jump in and get your feet wet.  VBA is a very powerful tool and 
you'll be thankful when you learned even a little of it.  Otto
"DebbieV" <dverras@westnet.com.au> wrote in message 
news:c94dfc15-3db3-41d1-87a4-bb00c9da8ec8@u16g2000pru.googlegroups.com...
On Oct 2, 1:36 am, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
wrote:
> Thanks Don. It's always good to see another view of it. Otto"Don Guillett" 
> <dguille...@austin.rr.com> wrote in message
>
> news:ua6ErppQKHA.2092@TK2MSFTNGP04.phx.gbl...
>
>
>
> > Only slightly tighter
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Address <> Range("b1").Address _
> > Or Target.Count > 1 Then Exit Sub
>
> > Application.EnableEvents = False
> > Target = Application.VLookup(Target, [coursenames], 2, 0)
> > 'OR
> > 'Target = Range("CourseNames"). _
> > 'Find(Target, LookAt:=xlWhole).Offset(, 1)
> > Application.EnableEvents = True
> > End Sub
>
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > dguille...@austin.rr.com
> > "Otto Moehrbach" <moehrbachoex...@bellsouth.net> wrote in message
> >news:%23RikOejQKHA.4580@TK2MSFTNGP06.phx.gbl...
> >> Debbie
> >> Are you saying that you want the user to select from a dropdown some
> >> course name, but you want Excel to display the course code in that same
> >> cell and not the course name selected? If so, yes, this can be done 
> >> with
> >> VBA (programming). I take it that your workbook has a list of course
> >> names and matching course codes?
> >> The following macro will do that. I assumed that your list of course
> >> names is in Column A of a sheet named "List" starting in A2 and the
> >> corresponding course codes are in Column B of the same sheet. Note that
> >> your setup of the Data Validation cell must include to not display an
> >> error when a "wrong" entry is made. Note that this macro must be placed
> >> in the sheet module of the sheet that contains the Data Validation 
> >> cell.
> >> Right-click on that sheet tab and select View Code. Paste this macro
> >> into that module. "X" out of the module to return to your sheet. HTH
> >> Otto
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> If Target.Count > 1 Then Exit Sub
> >> If IsEmpty(Target.Value) Then Exit Sub
> >> If Not Intersect(Target, Range("A1")) Is Nothing Then
> >> With Sheets("List")
> >> Application.EnableEvents = False
> >> Range("A1") = .Range("CourseNames"). _
> >> Find(What:=Target, LookAt:=xlWhole).Offset(, 1)
> >> Application.EnableEvents = True
> >> End With
> >> End If
> >> End Sub
> >> "DebbieV" <dver...@westnet.com.au> wrote in message
> >>news:8caf5d40-a6c6-4b2a-94a8-f5e698628dba@r24g2000prf.googlegroups.com...
> >>> Hi
> >>> I have a spreadsheet where I need to return a value which is an
> >>> alphanumerical code but the users know only the name.
>
> >>> Example:
>
> >>> In worksheet A there are details of clients. If the clients
> >>> participate in a course we are required to record the course code not
> >>> the name.
>
> >>> Column C is where I want the value to appear. What i would like to
> >>> occur is that the user selects from a drop down list the name of the
> >>> course but on selection the value returned is the course code.
>
> >>> Is this possible?- Hide quoted text -
>
> - Show quoted text -

Hi Otto & Don

Thanks for your responses.  I am quite the novice when it comes to
macros.  I have tried but to no success.  Is there any other way? I
think I may have to increase my knowledge if not.

cheers
Debbie 


0
10/4/2009 5:08:04 PM
Reply:

Similar Artilces:

no data to return blank
I currently have this formula =IF(C8>0,C8-$G$3,"") in a series of cells which is fine when there is information but I get a # value error when there is nothing in the cells it looks to. Is there a way to have it leave the cell blank if there is no data in the cells it looks to? -- A. when i test this formula using blank cells for c8 & g3 i get a blank cell as a result. how is your sheet arranged? "Andmor" wrote: > I currently have this formula =IF(C8>0,C8-$G$3,"") in a series of cells which > is fine when there is information ...

VBA to select all cells with content
Hi I need some advice with some VBA coding. I need to select all the cells in my worksheet which have content. Effectively selecting from A1 to the last cell in the last row , whichever it happens to be.. Can someone help with this? Thanks Try Range("A1").CurrentRegion.Select -- HTH Bob "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message news:LSmplIAFvRtLFwPL@chayes.demon.co.uk... > > Hi > > I need some advice with some VBA coding. > > I need to select all the cells in my worksheet which have content...

how to print reference label name in excel for comments?
I have chosen to print comments at the bottom of my spreadsheet the problems is it only gives the cell reference number. If I am looking at a printed version I would not know what cell it is refering to. I then labeled (reference) each cell but it still does not print out the name of the cell. How can I print out the label name oppose to $b$3$? Go to Print, Page Setup, Sheet, and select (under print), row and column headings. This will print out the headings just as you see them on your monitor. HTH Carole O "Needhelp" wrote: > I have chosen to print comments at the ...

No paste as CSV option in the Paste Special selection
A client of mine is running Excel 2000. When they select Paste Special, CSV is not available as an option. The data she's copying is not the problem since I have tested it here. Any comments/suggestions?? Thanks Mike When you copy, you're copying raw data, not a file. If you want to convert XL data to .CSV, you need to take the data to a separate workbook and do a File, Save As, and select CSV. ...

How do I get the extended file name of a file including server?
Using XP. I want to send file locations or shortcuts in an email rather than the actual file. How do I obtain the full extended file name including the server name and not just "G:" etc. so that other people in my workgroup can click on the file name and access the file immediately. If I understand correctly, you want to insert a link to a file rather than attach the file? This is assuming that the file is located somewhere that everyone can access it? (for example not in a home drive that only you have permission to) -- Kathleen Orland Outlook Tips: http://www.outlook-tip...

Conditional Formatting: comparing cell values
Using MS 2007, I have a column that has 40 sets of three rows; the rows are for three seperate years for 40 different schools that are included in the chart. The cells I am workign with show the performance indicator - a number - for each of the three years for the school in that set. I need to compare the three numbers for each set and show which set of three shows gains, and eventaully determine the highest performer over the three years. Sample: # SCHOOL YEAR 60 AVE 2008-09 61 AVE 2007-08 53 AVE 2006-07 69 BCE 2008-09 53 BCE 2007-08 56...

Chart.Name lives forever?
At the beginning of the code I blow away any chart objects I find with Dim co As ChartObject For Each co In mySheet.ChartObjects co.Delete Next And for good measure (this is probably all you have to do) mySheet.ChartObjects.Delete Afterwards, Chart.ChartObjects.Count is == 0 Later, I review the charts on the sheet with For Each co In ws.ChartObjects sTitle = co.Chart.Name MsgBox ("has the name " & sTitle) Next Only one chart is found, but it's name is incrementing. This leads me to believe that I really didn't b...

Dynamic Name Ranges in data validation
When I make a dynamic name range (with OFFSET) and use this in a data validation list, no error message appears when I manually type in data in the data validation cell. I don't have this problem with normal name ranges. Is there a way to solve this? Hans Hi Hans normally this should work. Can you post your name formula -- Regards Frank Kabel Frankfurt, Germany Hans wrote: > When I make a dynamic name range (with OFFSET) and use > this in a data validation list, no error message appears > when I manually type in data in the data validation cell. > I don't have thi...

Can I set up AutoComplete not to forget names for 30 days?
Outlook's "AutoComplete" for cc, bcc etc... seem to remember email addresses for a few days only. Can we extend the time? Thx Autocomplete does not "forget" names. The autocomplete cache stores up to 1000 names. What version of Outlook are you using? "MagicMusic9" <MagicMusic9@discussions.microsoft.com> wrote in message news:8657EEE7-74B2-4E3B-8DB8-5037F739E7F3@microsoft.com... > Outlook's "AutoComplete" for cc, bcc etc... seem to remember email > addresses > for a few days only. Can we extend the time? > > Thx > ...

Selective Editing of Nickname list
Anyone know if there's anyway to selectively purge or add to the *.nk2 file that is built by outlook to autocomplete e-mail addresses? I know how to start from scratch but it sure would be nice to edit the list. Thanks John K To remove a single entry, * start a new message * type the partial name you want removed * when outlook suggests a name, use the arrow keys to highlight the entry and press the delete key /neo ps - steps above are for outlook 2002/2003. "John K" <anonymous@discussions.microsoft.com> wrote in message news:1e11201c45554$28f11ac0$a601280a@phx.gb...

Protecting Selected Cells and Functions
I have a worksheet. In Cell B2 is a Data validation box Listing a range of colleagues names( DRop Down Menu). On selection of a name in B2, the contents of the whole worksheet changes. I like to Protect the worksheet for: 1) Hiding the formulaes 2) And most importantly preventing editing of the contents of any other cell (except B2). and yet be permiitted to: 3) Select contents in Cell B2 (Data Validation Box) 4) Select Auto filters in Row 4 I've tried using the the Tools/ Protect worksheet menu, ticking Select Lock Cells, Select Unlock cell, use auto filters. And in in Format/Cells/...

Null value to "0" / Currency format
I have a crosstab query in which my columns, based on [BidType], give totals based on the field LineTotalSellPrice. It returns the totals properly with the expression: LineTotalSellPrice: Sum(([Quantity]*([UnitPrice]+[QuoteCost]))*[Markup]) ...however in the instances where nothing exists, it returns Null, or blank. I'd like to convert this to a currency value of $0.00, so I changed my expression to: LineTotalSellPrice: Nz(Sum(([Quantity]*([UnitPrice]+[QuoteCost]))*[Markup]),0) ...and it returns the proper value, but not in a currency format (even though the field's format proper...

If XY is age/income to persons, how show names i.e by pointing?
If, in a scatter chart, each combination of X and Y has a name i.e. each XY is age/income to persons. It would be easier to read the chart if you could see the persons name by pointing at a XY-combination in the chart. How? You could add a label to every point. Use something like Rob Bovey's XY Chartlabeler (www.appspro.com) or John Walkenbach's Chart Tools (www.j- walk.com). You could then use an add-in to shrink/highlight the labels: Hover Chart Label http://www.tushar-mehta.com/excel/software/chart_hover_label/index.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, ...

Account not selected when coverting leads
There are 3 options to chose when converting a lead - account, contact and opportunity. If you do not select the account, can you do so at another time? Is there any way to create the account after converting if it was not selected? The Opportunity has to be linked to a particular account as it is a required field in Opportunity. The best thing to do is to create a default account and call it "None" or anything you like and get the users to set the opportunities that has no accounts when creating the opportunities to it as a temp account then insert the correct info in the...

Checking the Value of a Check Box in a Macro
I have a macro that hides a named range on rows. I also have a check box. If the check box is checked I want an error message to display and tell the user to clear the check boxes before teh rows will hide. ' ' Hide Spec_Plu_22_10_00 ' Sub Hide_Spec_Plu_22_10_00() 'Unprotect Time Sheet ActiveSheet.Unprotect If CheckBox135 = True Then MsgBox "Uncheck boxes in spec section your trying to close.", vbInformation, "Alert Message" GoTo 300 Else GoTo 200 End If 'Hide Rows 200 Range("Plu_22_10_00").Selec...

how would i change default user name all excel files
I am doing a learn direct course and had to use the help facility with question; Find out how you would change the default user name for all excel files. If somebody could help please email me on georginacambridg@aol.com Tools|Options|General Tab and modify the "user name" box learner wrote: > > I am doing a learn direct course and had to use the help facility with > question; > Find out how you would change the default user name for all excel files. > If somebody could help please email me on georginacambridg@aol.com -- Dave Peterson ...

Timesheet cube negative values Project Server 2007
Hi everyone, We are using Project Server 2007 SP2. When I generate a report from the Timesheet cube (in Excel or Data Analysis) to get the working billable and non-billable hours per period, per task, per resource I get negative values in the report. So far we found out the reason for the negative values is the correction by the timesheet manager. However the corrected hours in the timesheet are different than the hours generated in the report. Has anyone had any issues with this? As we are struggling with this for a couple of days now I will very much appreciate any help. ...

How do i select the coordinates of the first column & last row in a pasted range
This is a multi-part message in MIME format. ------=_NextPart_000_0023_01CA7DBE.CDD643B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable When i have a range pasted to a worksheet, how do i retrieve the = coordinates of the last row, first column of this range (the range = contains empty cells). Example of a range : B3:F32 =3D> the result shuld be B32 The range is not fixed, it could contain 1 row to ..... rows PS : The range is pasted and selected Thanx for your help. Luc ------=_NextPart_000_0023_01CA7DBE.CDD...

Subform comparison of 2 tables excluding non zero value
I am creating a subform (Access 2003/NT) in which the user will type in a Melt# (contained in the tblChemicalResults). I need to create a side by side comparison of the chemical requirements from the tblChemicalRequirements to the actual results contained in the tblChemicalResults, but I need it to exclude any non zero values from both tables. Each table contains all of the possible 14 different metal elements that could possibly go into making a particular part. Obviously, not all 14 go into every part, so how do I display only the elements that have a non zero value while also display...

Get Object ID of selected records
Hi, Is there any way to get object ID of all selected records from view. I need to pass objectId of all selected records to 3rd part site in query string on button click in CRM. Thanks, Pankaj Rana ...

Void a cash payout or drop
The void function only lets me void a sale. How can I void a payout? Merchants Consultings offer a wide range of consulting services, from system design to automation to reporting. If you need help with any aspect of RMS, chances are we can provide you with the knowlege you need. http://www.merchantsconsulting.com "rgj" wrote: > The void function only lets me void a sale. > How can I void a payout? > What if you do a "pay in" - ie do another payout but put a negative dollar amount matching the original pay out. I've never done this myself but it ...

Matching Names
Hi, I have a spreadsheet that contains a long list of staff names with data as follows: A B C Month Employee Name Monthly Salary From this list I want to create another 11 lists of monthly joiners and same for monthly leaversas follows: Monthly Joiners Lists: February Joiners March Joiners Employee Name Monthly Salary Employee Name Monthly Salary Under Employee Name I need a formulae that picks staff appear in February for the first time, and same applies for March, April, etc. Month...

Missing values from a pivot table field
I am creating a pivot table from an access database containing 896426 records. The issue I am running into is that on one of the fields the pivot table is only pulling in one of two values, but the access table does contain the second value. When I click on the arrow associated with that field it does not contain the second value either. Is there somewhere else in the settings this value could have been lost? Perhaps try Drag the field off the table Refresh the table Drag the field back on. With pivot tables you need to let us know which XL version you have. pivot ta...

PO Value against actual
Can RMS show what was the original value and quantity of the PO as against the recieved value and quantity? Sometimes there are some descrepancies between the actual PO and what was delivered. Does RMS record the difference and show it as well? ...

Calculating percentages of number of occurence of a text value
I have a field that is called txtpurpose. The value of this field in any record could be 4 different values " Re-financing", "Corporate Finance", "Buy- out", or "Restructuring" For a particular month (a field txtmonth) I want to count how many records had each of these values and what percentage that number was of the total number or records for that month. So I want to end up with a reuslt like this: Re-financing 46% Corporate Finance 38% Buy-out 12% Restructuring 4% Total 100% Can someone start me off in the right direction please? Many ...