Drop Down List Question #2

I have a drop down list from 5 cells merged together so as I can read the
length easier, I know I cant select a field from merged cells, is there any
way that when I select my choice I can get cells either side of my selection
A                  B                         F
12 Jan            Apples                 $12.00
17 Feb.             Pears                   $14.00
I have B Column as my range in drop down , how can I get the date and price
to move as well?  What I want to do is select Pears and the date, Item  and
price would move to say K L P Columns if my drop down list was in L column

--
Thanks in advance for your help....Bob Vance
..
..
..
..



0
rjvance (61)
1/25/2004 12:10:13 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
580 Views

Similar Articles

[PageSpeed] 42

Assuming dropdown is in L1, in K1, try

=INDEX(A1:A10,MATCH(L1,B1:B10,0),1)

and in P1 try

=INDEX(B1:F10,MATCH(L1,B1:B10,0),1)

By the way, you don't need to merge 5 cells, just widen the column.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Vance" <rjvance@Ihug.co.nz> wrote in message
news:buv1h6$u8s$1@lust.ihug.co.nz...
>
> I have a drop down list from 5 cells merged together so as I can read the
> length easier, I know I cant select a field from merged cells, is there
any
> way that when I select my choice I can get cells either side of my
selection
> A                  B                         F
> 12 Jan            Apples                 $12.00
> 17 Feb.             Pears                   $14.00
> I have B Column as my range in drop down , how can I get the date and
price
> to move as well?  What I want to do is select Pears and the date, Item
and
> price would move to say K L P Columns if my drop down list was in L column
>
> --
> Thanks in advance for your help....Bob Vance
> .
> .
> .
> .
>
>
>


0
bob.phillips1 (6510)
1/25/2004 12:41:34 AM
Thanx, I cant make columns wider as all my invoice work below is arranged
around standard sizing, that's why I merged the cells not to interfere with
my other work, is their any way I can make the drop down box wider so as to
read some thing bigger then "pears" like "Pears that will be exported"

--
Thanks in advance for your help....Bob Vance
..
..
..
..

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:eGhB8vt4DHA.2404@TK2MSFTNGP11.phx.gbl...
> Assuming dropdown is in L1, in K1, try
>
> =INDEX(A1:A10,MATCH(L1,B1:B10,0),1)
>
> and in P1 try
>
> =INDEX(B1:F10,MATCH(L1,B1:B10,0),1)
>
> By the way, you don't need to merge 5 cells, just widen the column.
>
> --
>
> HTH
>
> Bob Phillips
>     ... looking out across Poole Harbour to the Purbecks
> (remove nothere from the email address if mailing direct)
>
> "Bob Vance" <rjvance@Ihug.co.nz> wrote in message
> news:buv1h6$u8s$1@lust.ihug.co.nz...
> >
> > I have a drop down list from 5 cells merged together so as I can read
the
> > length easier, I know I cant select a field from merged cells, is there
> any
> > way that when I select my choice I can get cells either side of my
> selection
> > A                  B                         F
> > 12 Jan            Apples                 $12.00
> > 17 Feb.             Pears                   $14.00
> > I have B Column as my range in drop down , how can I get the date and
> price
> > to move as well?  What I want to do is select Pears and the date, Item
> and
> > price would move to say K L P Columns if my drop down list was in L
column
> >
> > --
> > Thanks in advance for your help....Bob Vance
> > .
> > .
> > .
> > .
> >
> >
> >
>
>


0
rjvance (61)
1/25/2004 1:01:21 AM
Bob,

I don't think so if you are using Data Validation. If you were using
Comboboxes, that is a layer above cells, so you can stretch that as much as
required, but it doesn't give you the functionality of DV, so not really
recommended.

Sorry, no further ideas.

Bob

"Bob Vance" <rjvance@Ihug.co.nz> wrote in message
news:buv4h6$r3$1@lust.ihug.co.nz...
> Thanx, I cant make columns wider as all my invoice work below is arranged
> around standard sizing, that's why I merged the cells not to interfere
with
> my other work, is their any way I can make the drop down box wider so as
to
> read some thing bigger then "pears" like "Pears that will be exported"
>
> --
> Thanks in advance for your help....Bob Vance
>


0
bob.phillips1 (6510)
1/25/2004 3:39:34 PM
You can use code to temporarily widen the column. There are instructions 
here:

   http://www.contextures.com/xlDataVal08.html#Wider

Bob Vance wrote:
> Thanx, I cant make columns wider as all my invoice work below is arranged
> around standard sizing, that's why I merged the cells not to interfere with
> my other work, is their any way I can make the drop down box wider so as to
> read some thing bigger then "pears" like "Pears that will be exported"
> 
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:eGhB8vt4DHA.2404@TK2MSFTNGP11.phx.gbl...
> 
>>Assuming dropdown is in L1, in K1, try
>>
>>=INDEX(A1:A10,MATCH(L1,B1:B10,0),1)
>>
>>and in P1 try
>>
>>=INDEX(B1:F10,MATCH(L1,B1:B10,0),1)
>>
>>By the way, you don't need to merge 5 cells, just widen the column.
>>
>>--
>>
>>HTH
>>
>>Bob Phillips
>>    ... looking out across Poole Harbour to the Purbecks
>>(remove nothere from the email address if mailing direct)
>>
>>"Bob Vance" <rjvance@Ihug.co.nz> wrote in message
>>news:buv1h6$u8s$1@lust.ihug.co.nz...
>>
>>>I have a drop down list from 5 cells merged together so as I can read
>>
> the
> 
>>>length easier, I know I cant select a field from merged cells, is there
>>
>>any
>>
>>>way that when I select my choice I can get cells either side of my
>>
>>selection
>>
>>>A                  B                         F
>>>12 Jan            Apples                 $12.00
>>>17 Feb.             Pears                   $14.00
>>>I have B Column as my range in drop down , how can I get the date and
>>
>>price
>>
>>>to move as well?  What I want to do is select Pears and the date, Item
>>
>>and
>>
>>>price would move to say K L P Columns if my drop down list was in L
>>
> column
> 
>>>--
>>>Thanks in advance for your help....Bob Vance
>>>.
>>>.
>>>.
>>>.
>>>
>>>
>>>
>>
>>
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
1/25/2004 6:45:37 PM
Reply:

Similar Artilces:

Customizing Activities / History List saving changes
Hi I have made some changes to CRM forms using formeditor. I do not know how will these changes show in my system. I have uploaded customization but its not working. Any suggestions, advice please Sarah did you publish the changes via Deployment Manager on the CRM server? -Gary "Sarah" <anonymous@discussions.microsoft.com> wrote in message news:62416116-EB6B-4CF8-AE4A-BBB4628F1923@microsoft.com... > Hi > I have made some changes to CRM forms using formeditor. I do not know how will these changes show in my system. I have uploaded customization but its not working. >...

Change Date #2
I have a rather large list of dates that are in a column and are written first as year then month then day Ex: 20051116. I want to change all of them in a date format like this 11/16/2005 with out retyping each one. I have tried to change the format but excel will not read it. I have tried to make a macro but excel only changed that one cell not the entire column. Please help. It's relatively easy, *IF* all your dates are entered the same. That means 4 digits for the year and 2 each for the month an day. You can understand the ambiguity of something like this: 2005121 Which could...

Drag and drop of eMail to own application
Hi everybody, i like to drag and drop an eMail of Outlook ( Express & Xp ) and Thunderbird into my own application ( MFC ). There'in the dropped eMail should be readed and the subject, body text, Recepiant, ... should be displayed into textboxes. I can check the different formats of the Drag-Event or Clipboard data. An Outlook Express mail is stored in plaintext into the clipboard, so it's seriously possible to extract it. But with Outlook XP Mails i see only the name of a .eml file ( the of file which would be saved on the Destop for example ) But how to extract the data? Do...

IMF #2
Is there a way to "tell" IMF notr to block our local emails? I mean not to block for example @microsoft.com Thanks On Tue, 8 Jun 2004 09:35:49 +0200, Ilia wrote: > Is there a way to "tell" IMF notr to block our local emails? I mean not to > block for example @microsoft.com Local mail will not be filtered, it will instead be assigned a SCL rating of "-1". -- Regards Henrik Walther Exchange MVP Exchange-faq.dk So how I have emails from inside the company in Junk folder? "Henrik Walther [MVP]" <spam@exchange-faq.dk> wrote in message...

Distribution list cannot receive external mail
Exchange 2003 Windows 2000 Server SP4 I have set up a distribution list and it works fine internally. It cannot, however, receive mail sent from an external address. An NDR is generated and I have included a copy below: This message was created automatically by mail delivery software. A message that you sent could not be delivered to one or more of its recipients. The following addresses failed: <XXXX@XXXX.com> SMTP error from remote server after RCPT command: host mail30.messagelabs.com[193.109.254.163]: 550-Invalid recipient <XXXX@XXX.com> 550 (#5.1.1) I have configu...

Drop Down List #9
I am trying to create a drop down list using two worksheets. How would I create this. One sheet would where the final product is and the other would be where the data is being kept. Watch this 5 min video: How to setup a data validation drop down list: http://youtube.com/watch?v=t2OsWJijrOM -- Biff Microsoft Excel MVP "Vincent Kerzman" <Vincent Kerzman@discussions.microsoft.com> wrote in message news:B2DAA48B-0BD2-4932-AE91-B86D55B8D8A6@microsoft.com... >I am trying to create a drop down list using two worksheets. How would I > create this. > > One s...

Users not appearing in Global Address List
Hi, I've created three new users all with mailboxes and they aren't appearing in the Global Address List. Can anyone tell me why? Thanks, Joe On Tue, 14 Sep 2004 16:39:52 +1000, "Joe Spenceley" <jspenceley@alwindows.com.au> wrote: >Hi, > >I've created three new users all with mailboxes and they aren't appearing in >the Global Address List. > >Can anyone tell me why? > >Thanks, >Joe > Check in ADU&C to see if an email address has been applied. See: http://blogs.msdn.com/exchange/archive/2004/07/07/175444.aspx And: http://s...

drag and drop
Using office XP I cannot drag and drop items from one folder to another in outlook 2002. Any ideas? Thanks ...

Filter list not correctly refreshed
Hi, I created a pivot table in Excel, linked to a database. So I added some filters to this table, displaying lists. The fact is that when I try to find a value in this list (my filter list), I don't find this value. I explain with more details. I add a new value to a list A in TFS (value x). I wait for the OLAP cube to refresh. I open my excel file, I check in the A list (used in my file as a filter) and try to select the value x, but I don't find it. My OLAP cube has correcty refreshed the data (I can see it with the displayed data in my tables). What should I d...

multiple sheets, need help please #2
I am not sure how to communicate what I am trying to ask, so please bear with me. I have two sheets in a estimating workbook. Sheet one is the worksheet, sheet two is the print out sheet for customers. In one cell on the print out sheet I want one of two things to show up. In the worksheet, the two things are in two seperate cells, and to select them you would have to input how many of each in a seperate cell next to them. A simple IF function won't work for what I need. Please let me know if you can help. Thank you very much. Hi Kile You'll have to be a lot more specific! -- ...

Bank #2
Could someone explain to me why anyone would want to download activity from the bank rather than entering it yourself when the transaction occurrs? Personally I use Fleet bank (or as I call them Fleece bank) and woudln't trust the downloaded information. They make too many mistakes, are very sloppy in their transactions. So why would anyone trust their downloads? -------------------------------------------------- 11 days until John Kerry is elected our next president. 11 days until George W. Bush is sent packing back to Crawford Texas. ----== Posted via Newsfeeds.Com - Unlimite...

drop down calendar #4
In a cell I want to have a drop down calendar of the current month. I downloaded a template of a calendar and tried data valadation but it only displayed the source range. Any ideas? TIA http://www.windowsdevcenter.com/pub/a/windows/2004/04/27/excelhacks.html "Ray A" wrote: > In a cell I want to have a drop down calendar of the current month. I > downloaded a template of a calendar and tried data valadation but it only > displayed the source range. Any ideas? > TIA > Ray See Ron de Bruin's site for this. http://www.rondebruin.nl/calendar.htm Gord D...

1/2 Page Numbers
I have created a Publisher Document that I am using as a manual. I will periodically publish updates to the manual and may need to insert pages. I would like to be able to number these new pages with decimals - for example, if I insert a two pages between pages 4 and 5, I would like to number the new pages 4.1 and 4.2. Is there anyway I can do this??? When I go into the format area it makes me choose a whole number. Apply a different Masterpage where the format for page numbering is 4.# Start the number over from 1 by inserting a section. Then on the following section restart the ...

Year End Closing #2
Hi It is noticed that some accounts(while does not have any transaction) are deleted while doing the year end close in Great Plains. Initially Reconcile and checklinks are done for each series. Immedietely after Year end close for financial series, many accounts are deleted from account master. Please anyone have idea about this? Thanks Jacob I believe accounts are removed that are marked as inactive and don't have a balance. "Microsoft News" <msnews.microsoft.com> wrote in message news:OMAjkKrPFHA.1884@TK2MSFTNGP15.phx.gbl... > Hi > It is noticed that some ac...

drop down data want to choose more than one item for cell
I have a drop down box that i want folks to be able to choose more than one item from the box to go into the cell. Multiselect from a DV dropdown is possible using VBA. See Debra Dalgleish's site for a downloadable sample workbook with event code. http://www.contextures.on.ca/excelfiles.html#DV0017 Note that you can have the selections in an adjacent cell or in the same cell. Gord Dibben MS Excel MVP On Fri, 26 Feb 2010 12:40:08 -0800, FranW <FranW@discussions.microsoft.com> wrote: >I have a drop down box that i want folks to be able to choose more than...

Money 98 Statement Import Problem #2
Hi, I'm having some trouble with my Money 98 Datafile. Ever since I imported a certain OFC file into money (at least, that's when I think it started), clicking on the "Update Acccount Register" button after choosing "Read Statement" in the Account register, does nothing. (As opposed to opening a window that allows you to update the account register.) Is it possible to clear/remove the imported statement, or to otherwise fix the problem? Thankyou, Jacob Buys (wjbuys@gmail.com) I'm not sure whether this will work right back to Money 98, but try going to the a...

Missing Contact List in Outllook 2002
User is trying to create a a distribution list in her Contact List, but found the contact list to be missing. How did this happen, and can it be replaced? Thanks. Herb Brown ...

Updating values in 2 forms
I have a script starting with the "FindAll" dialog that helps me search thru several sheet in a particular workbook. When I press "Find Next", the value of ActiveCell is updated in an UserForm1.txtLayout text box. Unfortunately, I dont know how to do it in any other workbook without inserting the following code it in all the sheets in the document. Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.txtLayout.Text = ActiveCell.Text End Sub If any idea please let me know!It is very frustrating! Compile succesfully! ...

Exchange question
I currently have Exchange 2000 on Windows 2000. I am getting ready to setup a new box with Exchange 2003 Ent. on Windows Server 2003. Here are my questions: 1) Does my Exchange server have to be in Native mode before I can upgrade? 2) If I change it to Native mode, will I need to reboot my server? 3) Do I need to reboot the server after I run the Forest and Domain Prep? Any help will be greatly appreciated. Thanks Kit It's been *a while* since I upgraded to Ex2003, but I believe the answers would be: No No No ------------------------ Chris Williams Sirana Software www.sir...

Drop Down List #2
Hi All Can this be done, as I am struggling. I have data in sheet B and want to create a drop down list in sheet A from this data, and I can't make it happen, I can create a drop down list in sheet A from data in sheet A but not from sheet B Could anyone help please Cheers Steve Name the range on the other sheet, and you can refer to it in the data validation dialog box. There are instructions here: http://www.contextures.com/xlDataVal01.html Steve wrote: > I have data in sheet B and want to create a drop down list in sheet A from > this data, and I can't make it h...

questions about money (current quicken user)
Hi I am thinking of changing from quicken to money. can anybody tell me is there any capability to share info with ms outlook, such as reminders etc. also is there a option for doing invoices. thanks tim Have a look here. May help http://money.mvps.org/faq/quicken.aspx -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "Tim" <adslk4at@tpg.com.au> wrote in message news:43eef869$1@dnews.tpgi.com.au... > Hi I am th...

Simple Newsgroup Questions
I'm new to Newsgroups. I'm subscribed to Microsoft.public.dotnet.languages.vb. I've posted 2 messages but I can't get the third to post. I saw it for a few minutes, then it was crossed out and said "no longer available from the server". Also, I can't seem to get more than 300 messages displayed. I'd like to be able to download and search through thousands. I've tried increasing the number of headers to download, but when I click on 'Download Next 1000 Headers', it seems to do nothing. In the microsoft.public.outlook newsgroup, I can see over...

Question of drop-down listbox
Hello, Is it possible to have listbox or combobox with Auto HScroll (ES_AUTOHSCROLL) capabilities. Thank You! I don't get it. ES_AUTOHSCROLL is an Edit control style, how would it apply to a listbox or combobox? What are you trying to do? AliR. "Ririko Horvath" <horvathr@securemethods.com> wrote in message news:uHUo%23OyLFHA.3336@TK2MSFTNGP10.phx.gbl... > Hello, > > Is it possible to have listbox or combobox with Auto HScroll > (ES_AUTOHSCROLL) capabilities. > > Thank You! > > I'm soory for not expressing my question properly...

550 5.7.1 Unable to relay #2
HI All! I have configured secured POP3 access on Exchange Server 2003 with SP1 (SBS 2003). It was working fine but now POP3 users are unable to send mail outside exchange organization. If they sends message outside exchange org. thier messages are returened with error 550 5.7.1 Unable to relay for xxx@yahoo.com or any domain name. I checked the event log there are two event id which are logged Event ID 1069 Source POP3SVC: SSPI authentication attempt from 24.17.207.164 has failed with error 0x8009030c. Event ID 7004 Source MS Exchange Transport: This is an SMTP protocol log for vi...

email address #2
How do I change my email address in outlook express? post in an outlook forum...this one is for Dynamics GP (Great Plains) ERP software -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "John" wrote: > How do I change my email address in outlook express? ...