Data Validation - Multiple columns in drop down?

In Access, I can have a drop down list based on another table and can
show several fields within that dropdown list but the value selected is
based on one field.  For Example, (in Access) a drop down list may
show: 721 SERVICE DEPARTMENT, but only the value 721 may be input into
the value selected.

Is there a way this can be done with the data validation within Excel?
I have a spreadsheet given to users as a type of electronic form that
is sent back to me for consolidation.  The users need more explanation
than I do; I already know that 721 is the SERVICE DEPARTMENT.

I did have two separate columns: one for 721 and another for SERVICE
DEPARTMENT but ended up concatenating the value to another column for
use in my data validation list.  This clutters the spreadsheet with
data I dont need.  I will eventually be exporting this information into
an Access database and want to keep the field sizes as small as
possible since a separate table can be linked to define that 721 will
equal "SERVICE DEPARTMENT"

Any Ideas?  Thanks!

0
8/14/2006 4:57:03 PM
excel 39879 articles. 2 followers. Follow

2 Replies
301 Views

Similar Articles

[PageSpeed] 59

You can do this with data validation and programming. There's a sample 
file here:

     http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0005 - Data Validation "Columns"'


bluegrassstateworker wrote:
> In Access, I can have a drop down list based on another table and can
> show several fields within that dropdown list but the value selected is
> based on one field.  For Example, (in Access) a drop down list may
> show: 721 SERVICE DEPARTMENT, but only the value 721 may be input into
> the value selected.
> 
> Is there a way this can be done with the data validation within Excel?
> I have a spreadsheet given to users as a type of electronic form that
> is sent back to me for consolidation.  The users need more explanation
> than I do; I already know that 721 is the SERVICE DEPARTMENT.
> 
> I did have two separate columns: one for 721 and another for SERVICE
> DEPARTMENT but ended up concatenating the value to another column for
> use in my data validation list.  This clutters the spreadsheet with
> data I dont need.  I will eventually be exporting this information into
> an Access database and want to keep the field sizes as small as
> possible since a separate table can be linked to define that 721 will
> equal "SERVICE DEPARTMENT"
> 
> Any Ideas?  Thanks!
> 


-- 
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
8/14/2006 11:07:47 PM
That did exactly what I was wanting it to.  Thanks so much!

Debra Dalgleish wrote:
> You can do this with data validation and programming. There's a sample
> file here:
>
>      http://www.contextures.com/excelfiles.html
>
> Under Data Validation, look for 'DV0005 - Data Validation "Columns"'
>
>
> bluegrassstateworker wrote:
> > In Access, I can have a drop down list based on another table and can
> > show several fields within that dropdown list but the value selected is
> > based on one field.  For Example, (in Access) a drop down list may
> > show: 721 SERVICE DEPARTMENT, but only the value 721 may be input into
> > the value selected.
> >
> > Is there a way this can be done with the data validation within Excel?
> > I have a spreadsheet given to users as a type of electronic form that
> > is sent back to me for consolidation.  The users need more explanation
> > than I do; I already know that 721 is the SERVICE DEPARTMENT.
> >
> > I did have two separate columns: one for 721 and another for SERVICE
> > DEPARTMENT but ended up concatenating the value to another column for
> > use in my data validation list.  This clutters the spreadsheet with
> > data I dont need.  I will eventually be exporting this information into
> > an Access database and want to keep the field sizes as small as
> > possible since a separate table can be linked to define that 721 will
> > equal "SERVICE DEPARTMENT"
> >
> > Any Ideas?  Thanks!
> >
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html

0
8/16/2006 5:07:41 PM
Reply:

Similar Artilces:

Columns for news groups
I would like to select my desired columns for all news groups. Is there any way to configure it for all news groups? Thanks, Scott Hi Scott, there is no way to batch-apply column settings to all the newsgroups. You need to modify them individually, I'm afraid. On 26/12/09 12:54, Scott wrote: > I would like to select my desired columns for all news groups. Is there any > way to configure it for all news groups? > > Thanks, > > Scott > -- Michel Bintener Microsoft MVP (Macintosh) *** Please always reply to the newsgroup. *** That is...

Find the first column to be 100%
I have an implementation section of a revenue template. Col B = Currently has the # of months until full utilization ( 7 months for example) Col C - has an error check, so that after the VPs make their changes they are supposed to go back and change the column B value, but they are not and I am being forced to correct the errors =IF(OFFSET(B21,0,B21+1)<>100%,"ERROR",IF(OFFSET(B21,0,B21)=100%,"ERROR","")) Starting in Column D through Column R are the manually entered percentages of utilziation, i.e. 0%,0%,5%,25%,50%,75%,100% for the remaining columns ...

Can I Move the default data location?
all I want to do is share the same data & personal files on 3 systems so that I can access the same mailbox at each location. I have looked at all of the sync & sharing programs for Outlook but all of them are a pain with the default location. If I move my data to a folder that could be shared easily I could then use that data file from all 3 systems. It is only my email and no one else uses it so I don't have security problem. -- Have A Great Day! Bill Meyer "Bill Meyer" <gingem@email.com> wrote in message news:euiGf5UJJHA.4628@TK2MSFTNGP05.phx.gbl... ...

No Data on Form Open
I have the following code on a cmdButton Dim strwhere As String strwhere = "[tbl_Reports].[FltPhase] = '" & Me![cboPhase] & "'" DoCmd.OpenForm "frm_MainView", acNormal, , strwhere DoCmd.Close acForm, "frm_SearchPhase" I would like to display a message "No data qualifies" and not open the form. Not sure what the If statement should read. -- Teach me to fish! Thanks for the help. Pax, M Hi, you can check recordset count on form's open event, and cancel it: if Me.Recordset.RecordCount =0 then Canc...

Easy way to make negative data positive?
I have a large section of negative data that I want to convert to positive i.e. - 2 becomes 2 etc. Is there a quick way to do this? Thanks! Sarah - Select a blank cell somewhere and enter -1. Edit | Copy. Select the range of cells containing numbers to be converted. Edit | Paste Special | Multiply. - Mike www.mikemiddleton.com "Sarah" <Sarah@discussions.microsoft.com> wrote in message news:5C18FE5F-99DE-4C95-AD89-492ABF2B31CC@microsoft.com... >I have a large section of negative data that I want to convert to positive > i.e. - 2 becomes 2 etc. Is there a quick w...

How do I set page breaks contingent upon certain data?
I am generating reports that list a lot of data for students in our school. I want to sort by teacher name and have Excel place a page break after each teacher's class. Is there a way to have Excel do this so that I am not having to do it manually each time the report is run? TIA ...

Opening multiple excel sessions
Ho w do I configure my excel so that when double click a .xls file from explorer it starts a brand new Excel session rather than adding it to an existing session? Reason I want to do this is that if I have multiple sheets open and some are set to manual calc and others are set to auto calc the calculation setting defaults to what ever it was on the first file opened. This is creating a problem when I open an auto calc file first then open a manual calc after as the manual one starts calcing upon opening it. Can anyone help me with this? -- sgreen ---------------------------------------...

Excel To Access: Transfer multiple rows from excel to access
Hi All, How can I transfer a Bock of data to Access from excel by clicking a "Submit" button? eg: I have designed a "Submit" button on the excel sheet that exports all of the student data into access in a single row... BUT I want to insert all this data in access in seperate rows. Here is what My excel table looks like: Student_ID Subjects Grades 123456 Eng A 123456 Hist B 123456 Math B+ 123456 Bio B- So, once we click "Submit" I want the data displayed above ...

Reference is not valid
Opening an excel worksheet (have many worksheets in a workbook) using a hyperlink from Word gets error "Reference is not valid". My hyperlink is filename#'worksheet!a1'. Help please. To my knowledge, you can only use hyperlink to open a workbook, not to a worksheet. "Crystal" <cgolding@aaptmobile.com.au> ????? news:0a3401c36155$80329960$a301280a@phx.gbl... > Opening an excel worksheet (have many worksheets in a > workbook) using a hyperlink from Word gets > error "Reference is not valid". My hyperlink is > filename#'worksheet!...

Email Address Verifier or Validator
I am looking for an add-on or standalone software for a company that has multiple computers on a local server that will allow them to confirm, verify and/or validate any incoming emails. Basically something like this. - User sends an email to Client. - The software then receives the email and holds it (keeps it from reaching the Client) in which it would then send an auto-response message to the User asking them to verify their email address by replying to that message. - The software will receive the message back and confirms that it is a valid email address thus releasing the original...

Cannot add columns to custom views
I have a problem that is causing a major handicap. I cannot add any columns to any custom leads view that I create. No matter what, the only column that is displayed is the default key column (name) that is there when the view is created. When I click on add columns, the dialog box that is supposed to list columns that I can add to the view has no columns listed at all. I am logged in as the System Administrator when trying to accomplish this. Also, when I create a filter criteria it is never saved. I save it, but when I go back into it, it is gone. Any help would be GREATLY apprec...

Separating Data #5
Dave - you are my hero!! Even though this is a time consuming proces up front, it gets the data where it needs to be. It was well worth i for me since I had over 35,000 rows of info. Thanks again.. -- Boop91 ----------------------------------------------------------------------- Boop914's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=312 View this thread: http://www.excelforum.com/showthread.php?threadid=27626 Don't tell me you finished in less than 24 hours! Time to check and recheck--there's just too much variation to trust your first efforts! &...

Id over multiple nodes
Is it possible to create an id over multiple nodes? I would like to use generate-id which includes all of the Route nodes which has a direction of 1. So I don't want to for-each each Route node and run generate-id on each on of them but bulk them all together and generate an id for the nodes <Routes> <Route> <City1>A</City1> <City2>B</City2> <DateTime1>10:00</DateTime1> <DateTime2>11:15</DateTime2> <Direction>1</Direction> </Route> <Route> <City...

Applying a vlookup formula to an entire column
Hello. I've got a 3000 column spreadsheet where I'm trying to match fields from an existing set of informationl. =VLOOKUP(A2,F2:I1997,2,FALSE) Above is the formula I'm using to search an existing section of information (F2:I1997) and comparing it to information in column A. I want my results to show up in column b. When I let Excel autofill the formula, It changes my table_array (F2:I1997) as well as my lookup_value (A2). How do I change it so that it just changes the lookup_value as it goes down without reentering it 3000 times? Thanks for the help. Dio. use absolutes for yo...

How can I hide/unhide columns with those neat + and
Hi, I wonder how can I insert those neat little + and - signs that allows users of the sheet to quickly hide and unhide set of rows/columns? Would be nice feature to learn, but I don't even know what it is called, so it makes it hard to search from help or google =) -Sirritys Select the rows to group, Data>Group And Outline>Group -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sirritys" <aki.koikkalainen@hotmail.com> wrote in message news:1154416673.707982.87000@p79g2000cwp.googlegroups.com... > Hi, > > I wo...

Cell Data Change to Trigger Macro
Is there a way to trigger a macro whenever data changes in a cell? The value in the cell will be varying numbers, so it can't be TRUE/FALSE etc. Thanks Mike, Yes. Put it in the sheet module, instead of a regular module. It should look like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then MsgBox "You changed cell A1, you did." End If If Not Intersect(Target, Range("A1").EntireColumn) Is Nothing Then MsgBox "You changed something in column A. You sure did." End If End ...

pull data from one row if another row contains certain text
Can someone please get me started on this? I have spreadsheet that I want to pull text in one row of another row contains "Example". A B C 1 45 Example 2 34 Blablabla 3 56 Example Would return 45 56 -- TIA Kevin HI Try this =IF(C1="Example",A1,"") HTH John "coasterdude" <coasterdude@discussions.microsoft.com> wrote in message news:85888F99-C0AB-4827-8B95-FA71EC64071A@microsoft.com... > Can someone please get me started on this? I have spreadsheet that I want > to > pull text i...

Multiple Freezes in a sheet
Is it possible to put multiple freezes in an excel sheet? Depends what 'freeze' means in your vocabulary. It means nothing at all in normal Excel usage. "amag" <amitntu@gmail.com> wrote in message news:1132544656.124787.190210@o13g2000cwo.googlegroups.com... > Is it possible to put multiple freezes in an excel sheet? > ohh...what i meant to ask was Is it possible to put multiple freeze panes(Alt W F) in an Excel Sheet? Hi No, you can't have multiple Freeze Panes. Two possible workarounds Either copy the row you want to watch (totals ??) to the top...

Problem retrieving data using queries in a simple (one table) data
I've been asked to create queries (for example all songs by an artist) in a CD Collection database. The query can only retrieve some songs for some artists and no songs at all for other artists. I've checked the query which seems ok. I think the problem could be because it is a simple database (just one table with about 50 columns and about 100,000 records. I tried to use "Analyse --- Table" Tool but have not been successful. To avoid corrupting the database, I've decided to get advice from you wonderful people out there before doing anything else. Can I change th...

Meetings Dropping off Organizer's Calendar
I have users that are having meetings drop off their calendars AFTER they send an update to the meeting....any ideas? Outlook 2007 - Thanks! Are they syncing with a smartphone? Does it only happen with updates? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Do you sync your mailbox wi...

Numbers Organized As Data
HI, When I download financial numbers from a database to an excel spreadsheet, the numbers are automatically organized like if they are data ex. 1.28000000 into 128.000.000. I would like to have them as numbers no data. What do I have to do in order the permanently fix the problem? Thank you. air -- campione ------------------------------------------------------------------------ campione's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36758 View this thread: http://www.excelforum.com/showthread.php?threadid=564773 ...

Transfer multiple orders to one invoice?
Does anyone know of any solution that would allow transfer of line items from multiple SOP orders to one invoice? Hi, Are you interested in custom solutions. If so please drop a email. Regards, Manick manick.m@hotmail.com "VGrinam" wrote: > Does anyone know of any solution that would allow transfer of line items from > multiple SOP orders to one invoice? I believe Binary Stream has a solution for this. -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "manick" <manick@discussions.microsoft.com> wrote in message news:D039D9ED-C7D0-4AFE-9B0C-...

How do I find a column entry closest to a particular value
I'm trying to find the closest value in a column to a specified number, then list it. Basically, I have a function in which I have to find 25% of the max value and find which value in the data column that is closest. I was trying to use a function to search the column but it wasn't working. any thoughts? Thanks, Dave Try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER... =INDEX(A1:A10,MATCH(MIN(ABS(A1:A10-B1)),ABS(A1:A10-B1),0)) ....where B1 contains the specified number. Hope this help! In article <D67E4AC2-5F3A-42F2-9689-33FBF2AB8BC8@microsof...

How can I find the common names in two columns of names?
I have two columns of names. I want to find if there are any common names in these two columns, and if there are I want to figure out which ones are common. The only solution I could think of so far is using the find function in Excel by entering each name individually from the second column and searching them in the first column. It works, but considering the fact that I have more than 250 names in the second column and 990 names in the first one, I just wanted to know if there is a simpler way. I also have the SSN of these individuals as separate columns, in case I might need nume...

The command line argument is not valid. Verify the switches you are using."error
What might be the possible cause of the "The command line argument is not valid. Verify the switches you are using." error within Outlook. We have a VB program that uses linking to email function, by and large they all work fine. However, on certain computers , using that function produces the above error message. I can't understand why this error pops up on others. Please advise. Any answer would be appreciated. Many thanks Mheng I think we'd need to know a lot more information about what your VB program is trying to do in order to be of any help....also need t...