Defined range using more than one column

Can anyone tell me if it is possible to have two or more columns in a 
defined range. I want to have a dropdown validation list where two or more 
columns of data is displayed on the dropdown list. When an item is selected 
in the dropdown list only one of the columns data is displayed in the cell. 
Is this possible and how is this accomplished?
Or if there is a different approach I should go down please let me know.

Thanks
Pat 


0
1/18/2005 12:42:04 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
448 Views

Similar Articles

[PageSpeed] 35

You can have a defined range with two or more columns, but that won't show
up in Data=>Validation using the list option.  You might want to look at the
Control Toolbox Toolbar Combobox or Listbox.

> When an item is selected
> in the dropdown list only one of the columns data is displayed in the
cell.

It is unclear if you are saying this is what you want, or you are
complaining that this is what happens and not what you want.  It certainly
is what happens and there is no way around that unless you use code to
interpret the selection and write a concatenated string of the column values
to the single cell or write the row in the combobox dropdown across multiple
cells.

-- 
Regards,
Tom Ogilvy


"Pat" <glass_patrick@hotmail.com> wrote in message
news:uyoKgsV$EHA.600@TK2MSFTNGP09.phx.gbl...
> Can anyone tell me if it is possible to have two or more columns in a
> defined range. I want to have a dropdown validation list where two or more
> columns of data is displayed on the dropdown list. When an item is
selected
> in the dropdown list only one of the columns data is displayed in the
cell.
> Is this possible and how is this accomplished?
> Or if there is a different approach I should go down please let me know.
>
> Thanks
> Pat
>
>


0
twogilvy (1078)
1/18/2005 12:56:13 PM
Try SUMIF. For example, let's say your dates are in col. 
A, values to sum in col. B, and all this is in a sheet 
named "mysheet". With a date in A1 of a new sheet, use:

=SUMIF(mysheet!A:A,A1,mysheet!B:B)

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Can anyone tell me if it is possible to have two or more 
columns in a 
>defined range. I want to have a dropdown validation list 
where two or more 
>columns of data is displayed on the dropdown list. When 
an item is selected 
>in the dropdown list only one of the columns data is 
displayed in the cell. 
>Is this possible and how is this accomplished?
>Or if there is a different approach I should go down 
please let me know.
>
>Thanks
>Pat 
>
>
>.
>
0
jasonjmorin (551)
1/18/2005 1:52:09 PM
Ignore this. I responded to the wrong post. Sorry.

Jason

>-----Original Message-----
>Try SUMIF. For example, let's say your dates are in col. 
>A, values to sum in col. B, and all this is in a sheet 
>named "mysheet". With a date in A1 of a new sheet, use:
>
>=SUMIF(mysheet!A:A,A1,mysheet!B:B)
>
>HTH
>Jason
>Atlanta, GA
>
>>-----Original Message-----
>>Can anyone tell me if it is possible to have two or 
more 
>columns in a 
>>defined range. I want to have a dropdown validation 
list 
>where two or more 
>>columns of data is displayed on the dropdown list. When 
>an item is selected 
>>in the dropdown list only one of the columns data is 
>displayed in the cell. 
>>Is this possible and how is this accomplished?
>>Or if there is a different approach I should go down 
>please let me know.
>>
>>Thanks
>>Pat 
>>
>>
>>.
>>
>.
>
0
anonymous (74722)
1/18/2005 2:23:15 PM
> It is unclear if you are saying this is what you want, or you are
> complaining that this is what happens and not what you want.  It certainly
> is what happens and there is no way around that unless you use code to
> interpret the selection and write a concatenated string of the column 
> values
> to the single cell or write the row in the combobox dropdown across 
> multiple
> cells

Yes this what I want and I am happy with that.

Do you happen to know if there is examples of what you suggest on the web?


"Tom Ogilvy" <twogilvy@msn.com> wrote in message 
news:Okx8Y0V$EHA.3416@TK2MSFTNGP09.phx.gbl...
> You can have a defined range with two or more columns, but that won't show
> up in Data=>Validation using the list option.  You might want to look at 
> the
> Control Toolbox Toolbar Combobox or Listbox.
>
>> When an item is selected
>> in the dropdown list only one of the columns data is displayed in the
> cell.
>
> It is unclear if you are saying this is what you want, or you are
> complaining that this is what happens and not what you want.  It certainly
> is what happens and there is no way around that unless you use code to
> interpret the selection and write a concatenated string of the column 
> values
> to the single cell or write the row in the combobox dropdown across 
> multiple
> cells.
>
> -- 
> Regards,
> Tom Ogilvy
>
>
> "Pat" <glass_patrick@hotmail.com> wrote in message
> news:uyoKgsV$EHA.600@TK2MSFTNGP09.phx.gbl...
>> Can anyone tell me if it is possible to have two or more columns in a
>> defined range. I want to have a dropdown validation list where two or 
>> more
>> columns of data is displayed on the dropdown list. When an item is
> selected
>> in the dropdown list only one of the columns data is displayed in the
> cell.
>> Is this possible and how is this accomplished?
>> Or if there is a different approach I should go down please let me know.
>>
>> Thanks
>> Pat
>>
>>
>
> 


0
1/18/2005 2:29:13 PM
I have tried you suggestion and was unsuccessful  only a date 00-01-1900 was 
returned from the formula. Am I missing something here?


"Jason Morin" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message 
news:133301c4fd64$e7a1e400$a501280a@phx.gbl...
> Try SUMIF. For example, let's say your dates are in col.
> A, values to sum in col. B, and all this is in a sheet
> named "mysheet". With a date in A1 of a new sheet, use:
>
> =SUMIF(mysheet!A:A,A1,mysheet!B:B)
>
> HTH
> Jason
> Atlanta, GA
>
>>-----Original Message-----
>>Can anyone tell me if it is possible to have two or more
> columns in a
>>defined range. I want to have a dropdown validation list
> where two or more
>>columns of data is displayed on the dropdown list. When
> an item is selected
>>in the dropdown list only one of the columns data is
> displayed in the cell.
>>Is this possible and how is this accomplished?
>>Or if there is a different approach I should go down
> please let me know.
>>
>>Thanks
>>Pat
>>
>>
>>.
>> 


0
1/18/2005 2:33:27 PM
Reply:

Similar Artilces:

How can I email an excel file using my outlook contacts?
"cheflady" <cheflady@discussions.microsoft.com> wrote in message news:F9C13E60-150D-4F03-B519-A8769F08CD0F@microsoft.com... > > <sigh> Please use the big white space to write your question........ exactly the same way you email ANY file..... go to file\send\mail recipient( as attachment) "Gordon" wrote: > "cheflady" <cheflady@discussions.microsoft.com> wrote in message > news:F9C13E60-150D-4F03-B519-A8769F08CD0F@microsoft.com... > > > > > > > <sigh> Please use the big white space to write you...

Changing Function to use a Date Input
Hi. can anyone please advise me on how to change the function keys.. sa F10, F11 & F12 to use to input a date into a cell. EG. F10 to be 01/01/04 F11 to be 02/01/04 F12 to be 03/01/04 What I require is to go to any cell and press the relevant function ke to Input the associated date Any help would be greatly appreciate Many thank Rob PS using Excel 200 -- Message posted from http://www.ExcelForum.com First, I'm not sure if you entered Jan 1, 2004, Feb 1, 2004, and Mar 1, 2004 or Jan 1st-3rd, 2004. Here are a couple of macros. The first turns this on, the second turns it...

Current time for data entered in column
Whenever data is entered in a cell, current time should come auto i another corresponding cell. For example, time should automaticall come in Y column for any data entered in any cell of the column A. Fo A10 time should come in Y10 and for A12 it should come in Y12 and s on. I need it for doing time and motion study. Appreciate all help -- Message posted from http://www.ExcelForum.com Hi Mohitmahajan! Based on JE McGimpsey (http://www.mcgimpsey.com/excel/timestamp.html) Let's say that every time an entry is made in cells A2:A100, the corresponding cell in column Y should have the...

unknown range
I want to write a macro that selects a range that starts in A3 and ends in Jsomewhere. Is it possible to refer to an unknown cell in an known column? It might be nice to know what determines the "unknown" cell. =offset($a$3,0,0,counta($J:$J),10) could be a defined name range to find the last cell in J based on data in J -- Don Guillett SalesAid Software dguillett1@austin.rr.com "fanny" <f.klompsma@chello.nl> wrote in message news:76373$45b0fa73$3ea35143$7359@news.chello.nl... >I want to write a macro that selects a range that starts in A3 and ends in >J...

tender using outside gift card
Hi, does anyone know how to 'pass' the transaction total to an outside exe or dll? we are trying to process the transaction with a gift card (not an internal gift card, this GC is another company who wants to 'share' the gift card balance). basically RMS needs to pass the transaction total to the exe (or dll) which will query the balance of the gift card (over the internet and/or local DB). the exe/dll will query its database and return a yes or no to RMS any help would be appreciated. scott@kanesvending.com -- Walt You use what's called a "Hook" function t...

Transferring data between worksheets using Sheet Command?
Hi for an assignment i have to enter grades for 200 students in 4 subjects. there is a front summary sheet that contains all the subjects and all the students and their overall grade GPA etc.. anyway this summary sheet has to be populated automatically from the individual math, english etc.. worksheets. The guide says to do it using the 'sheet command' any help greatly appreciated. Thanks ...

user-defined data type capabilities
It seems I use Microsoft Visual Basic 6.5 from Microsoft Excel 2003. I've just introduced my first user-defined data type (UDT) to a module. I found it greatly simplified passing parameters. Private Type Journey Depart As String Arrive As String End Type What can I do with such UDTs? 1) Declare functions and scalar, array, and parameter variables. 2) Assign e.g. Dim A as UDT, B as UDT: A = B 3) Not compare variables. e.g. if UDT0 = UDT1 ... gets an error. What else can UDTs be used for? When, in the Visual Basic Editor (VBE), I hover over code referencing a v...

moving data from one sheet to another
I have records in sheet1 as follows: A B C D E 1 NAME SEX SUBJECT COLLEGE WHETHER SELECTED 2 A MALE MATHS XAVIER YES 3 B FEMALE ENGLISH SEBASTIAN 4 C FEMALE MATHS PAULS 5 D MALE PHYSICS XAVIER YES 6 E MALE CHEMISTRY PAULS YES 7 F MALE PHYSICS SEBASTIAN YES 8 G FEMALE MATHS XAVIER YES 9 H MALE PHYSICS PAULS 10 I MALE MATHS SEBASTIAN Now, I want to scan entire data upto last cell of the range and move(cut and paste) those of the selected candidates, Sheet2 of the same book.After moving, the name o...

User-Defined type not defined
hello I use ACCESS 2000. I'd like to run the following code borrowed from one of the Access user sites, but compiling fails at the Dim statement. ("User-defined type not defined") Private Sub ListAvailPrinters() Dim prn As Printer For Each prn In Application.Printers Debug.Print prn.DeviceName & " on " & prn.Port Next prn End Sub I suspect a Reference Library is not installed, but I'm not sure which one I need. I currently have the following Reference Libraries: Visual Basic For Applications Microsoft Acce...

Using 'Add to Favourites' option on a shared calendar not working
In Outlook 2007 we have a few users who are unable to add a shared calendar to their Calendars->Other Calendars list. The steps that work on a mojority of computers are: 1. Go->Folder List. 2. Navigate to Public Folders->All Public Folders->...->Communal Calendar. 3. Right Click on Communal Calendar and select 'Add to Favourites'. 4. Click OK on 'Add to Favourites' dialog. 5. Go->Calendar and the calendar should have appeared under 'Other Calendars' list. However, in a few cases when OK is clicked in step 4 the calendar never appears in...

How do I change headings for columns from numbers to letters? #2
...

one sided posting entry in gp?
how would I create a one sided entry in GP to correct a double sided entry that posted twice ( due to a system failer a month ago)? There used to be a document on customer source; but i cant find it. Thanks for any information I'm unclear as to why you need a one sided entry instead of backing out one of the double sided entries that posted incorrectly. To backout one of those entries click "Correct" on the JE Transaction Entry Screen (Transactions->Financial->General) Mark (DynamicAccounting.net) On Sep 18, 12:54 pm, 2020 <2...@discussions.microsoft.com> wro...

CALCULATING DEPRECIATION USING ACCESS
HOW DO I DO IT? Why don't you ask the Access newsgroup instead of the Microsft CRM ng? -- Brandon IT Director Office Equipment & Supplies at http://www.presentationsdirect.com "PATRICK" <PATRICK@GLWHOLESALE.COM> wrote in message news:026d01c352d1$755a23b0$a101280a@phx.gbl... > HOW DO I DO IT? ...

How to use this DeleteEMFs() Macro
This macro was on the MicroSoft website as a work around for the .emf storage problem http://support.microsoft.com/default.aspx?scid=kb;en-us;299372 What exactly does this macro do and how should I use it? I want to remove all the .emf files from a particular Excel File. B/c the workbook creates hundreds or thousands of temp .emf files everytime it opens and it takes forever to open.. Thank you very much for any help! george Private Sub Workbook_Open() Call DeleteEMFs End Sub Private Sub DeleteEMFs() Dim fso As Variant Set fso = CreateObject("Scripting.FileSystemObject"...

combination stacked column and line data
Help. I've been asked to create a chart that has two sets of data that are stacked (columns) and 5 sets of data that display in line format. Is this an option in Excel. I can do a columan and a line but can't seem to add more than that. I'm using Excel 97 (but have access to Excel 2003). Please and thanks. Don't spend a lot of time looking for every last permutation in the "built in custom" chart types. What you probably want isn't there, but it's easy to roll your own type. Make a chart with all data plotted in one format (probably line, so you ha...

Batch Naming of ranges
Dear all, There are 80 sheets in my workbook. I want to name the ranges A1:B20 with systematic names in these 80 sheets, say, student_01 for Sheet1!A1:B20, student_02 for Sheet2!A1:B20, ..., student_80 for Sheet80!A1:B20. Can I name these ranges easily? Thanks. Best Regards, Andy Dim i As Long For i = 1 To 80 Worksheets("Sheet" & i).Range("A1:B20").Name = "student_" & Format(i, "00") Next i -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy Chan" <chankhandy-msnewsgroup...

cell value goes up by one count
,Hello Again Could i get some help with this. How does one get an activecell , that has a value of 1, that is copied from sheet1 to sheet2 so that the value changes from 1 to a 2 and so on depending on how many times I copy a sheet. I hope this makes sense Thanks in Advance Allan =sheet1!(a1). I think you are asking for this formula. It will change the value of current cell (doesn't matter on which sheet you are currently working) to value of Sheet 1's A1 cell. Means if Sheet 1 A1 = 100 and you are currently working on Sheet 2 F1, then this formula will show the F1 value ...

Excel range truncates when Pasted as Picture to PPT & Word
Hi... I have been trying to copy an Excel Spreadsheet into PPT, but have had problems. In order to solve it, I created new .xls and .ppt files to create a test, but got the same problems... The following steps recreate the problem: 1) In a blank spreadsheet, I placed a single number in each cell, starting at A1 and going across to AS, until there are 1 through 45 across. Format them in some way... say Red text with an underline. 2) Set the width of all the columns to 2.00 (0.11 inches). 3) Select A1:AS 4) Copy (or Add to Scrapbook) -- The result is the same with both. 5) From Scrapb...

sorting two columns of merged cells
I have two columns of merged cells. two cells in each column are merged in each row: ie A1 and B1 are merged into one cell, a2 and B2, etc. The next column is the same; C1 and D1 are merged, C2 and D2, etc. Is there any way I can sort these columns? I need a descending sort by col A and B. Im using Excel 2003 Hope this is clear. Jim Don't merge cells. Look in the archives of this group for countless reasons why not; you've just found one of them. -- David Biddulph "bigjim" <bigjim@discussions.microsoft.com> wrote in message news:...

Using cell value in VBA sub
I'm using the following sub Sub FindRow() Dim rngFound As Range Set rngFound = Range("A:A").Cells.Find(What:="test", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry ""test"" was not found" Else rngFound.EntireRow.Select End If End Sub My problem is that if I excange Test with a1 - wanting to look the value in cell a1 I always get the first blank a-cel...

Complex Query question
I've got a challenging query I need help with! Here's a snapshot of the tables and relationships. I've put the relevant tables into this query layout just for illustrative purposes. ----- click on it to make it bigger (it's still a little hard to read) http://tinypic.com/view.php?pic=6xu9oh4&s=1 Note - the table names start with phrases that are not relevant to this question (ie I use the word "record" for another use than the normal database usage of that word). ------ I'll explain the setup. I want the Table Record-Orders-Sales to lookup Record-Cur...

Outlook 2003 hangs when using Word 2003 as email editor
I'm having a recent problem with Outlook 2003. When using Word 2003 as email editor and attempting to reply to a message, my application hangs for a long time. I then can release it only but closing Word through task manager. Microsoft is evidently aware of this problem by posting "Microsoft Knowledge Base Article - 278214," but without providing a resolution or workaround. I've repaired Outlook and uninstalled both applications, then reinstalled. I've deleted the 2 add-ins and installed Outlook and Word without any success. Can anyone help me? I really li...

add info to column
I have a list of over 1000 names in my a column but need to add @gmail.com to the end of all of them. How can i do that in excel? HI Lets assume your list is starting at A1, just type in B1 =A1&"@gmail.com" and copy down to A1000. HTH John "jj348206" <jj348206@discussions.microsoft.com> wrote in message news:E7E6911F-BE61-422D-BBA7-BCFB83A66656@microsoft.com... >I have a list of over 1000 names in my a column but need to add @gmail.com to > the end of all of them. How can i do that in excel? Assume names running in A2 down In B2: =IF(TRIM(...

Use of Indirect
I am trying to do something similar to what I have seen on one of these discussions. I have sheetnames of the other worksheets in a workbook in row 1 of a worksheet and am wanting to refer to a formula in cell A33 of each of the works - I've always had problems getting my head around this "INDIRECT" function. For instance in D31 I'm trying to get the result ="SheetnameD1"!A33 using something like: =INDIRECT("'"&D1&"'!A33",), but there are two problems with this, 1 this returns a #REF! error, and 2 copying it across the rows wil...

How do I make a stacked column chart with side-by-side comparison.
Looking at making a stacked column chart. But I want it to look like a clustered column, able to compare 2 items next to each other. Any thoughts? Take a look at Jon Peltier's site. He has lots of examples of different chart types. I'll start you out on the page you might want to look at. http://www.peltiertech.com/Excel/Charts/ComboCharts.html "Tim" wrote: > Looking at making a stacked column chart. But I want it to look like a > clustered column, able to compare 2 items next to each other. Any thoughts? Here's a page with links to clustered-stacke...