Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-05-2012, 07:59 AM
Geza59 Geza59 is offline How to "hard link" two adjacent cells to a data validation drop down list? Windows XP How to "hard link" two adjacent cells to a data validation drop down list? Office 2003
Novice
How to "hard link" two adjacent cells to a data validation drop down list?
 
Join Date: May 2012
Location: Budapest, Hungary
Posts: 20
Geza59 is on a distinguished road
Default How to "hard link" two adjacent cells to a data validation drop down list?

Hello to all!

As you can see on the attached Excel file I created a data validating drop down list (B2 -B5) and cannot figure out how to modify the list of the cells in the drop down list so that when I choose an item from the drop down list, not just the current cell but the two cells next right to it would also get updated.

For example, if I choose John from the drop down list, I'd like his building and apartment number also pop in once I let the mouse button go on John:

John A 1



So, maybe I should ask the question: How can you create a drop down list of not one column but two or three columns of lists?

Why I need this feature on the first place instead of merging the building and apartment number info in the first cell? So, that later I could do data sorting based on the building name or apartment numbers.
Attached Files
File Type: xls Data_Validation.xls (13.5 KB, 12 views)
Reply With Quote
  #2  
Old 10-05-2012, 05:46 PM
grizz grizz is offline How to "hard link" two adjacent cells to a data validation drop down list? Windows XP How to "hard link" two adjacent cells to a data validation drop down list? Office 2003
Novice
 
Join Date: Jan 2012
Posts: 28
grizz is on a distinguished road
Default

will this work for you
Attached Files
File Type: xls Data_Validation(1).xls (22.0 KB, 17 views)
Reply With Quote
  #3  
Old 10-06-2012, 02:06 PM
Geza59 Geza59 is offline How to "hard link" two adjacent cells to a data validation drop down list? Windows XP How to "hard link" two adjacent cells to a data validation drop down list? Office 2003
Novice
How to "hard link" two adjacent cells to a data validation drop down list?
 
Join Date: May 2012
Location: Budapest, Hungary
Posts: 20
Geza59 is on a distinguished road
Question

Wow Grizz!

No wonder, why I was clueless about how to do it. I will have to spend time on analyzing your elegant solution. But until then, would you please, explain to me just one thing:

According to Excel help, the syntax of the function Index can be either 3 or 4 items in a bracket but your solution contains only two:

a range and a Match expression. How about that?

It's true, that the Match expression contains 3 items but I am assuming that the result of the Match expression is only one item, so that's why I am puzzled a bit here.

As I said, I will definitely have to learn how to use the Index and Match (one at a time), but without your answer, I could not figure out the mismatch in the required number of items in your Index expression.
Reply With Quote
  #4  
Old 10-07-2012, 12:59 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How to "hard link" two adjacent cells to a data validation drop down list? Windows 7 64bit How to "hard link" two adjacent cells to a data validation drop down list? Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,484
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

I strongly recommend reading this article on the INDEX function, just to remind that INDEX does in fact NOT return a cell value but a reference....
Reply With Quote
  #5  
Old 10-08-2012, 10:52 PM
Geza59 Geza59 is offline How to "hard link" two adjacent cells to a data validation drop down list? Windows XP How to "hard link" two adjacent cells to a data validation drop down list? Office 2003
Novice
How to "hard link" two adjacent cells to a data validation drop down list?
 
Join Date: May 2012
Location: Budapest, Hungary
Posts: 20
Geza59 is on a distinguished road
Default

Grizz, Thank you for your solution, I tried to make it work in my problem but I couldn't make it work.

The only difference between the sample spreadsheet that I uploaded here and the one that I need the solution for is that the "look up" table is located on a different sheet.

When I created the sample spreadsheet, I put the look up table on the same sheet where the main information is located just to keep it simple but I guess, my problem requires a different solution when the look up table is on a different sheet.

Am I right, or I am just missing out on something?

Pecoflyer: thank you for the link to that page, indeed it is a great page about the Index function.

Last edited by Geza59; 10-09-2012 at 12:07 AM.
Reply With Quote
  #6  
Old 10-10-2012, 05:00 PM
grizz grizz is offline How to "hard link" two adjacent cells to a data validation drop down list? Windows XP How to "hard link" two adjacent cells to a data validation drop down list? Office 2003
Novice
 
Join Date: Jan 2012
Posts: 28
grizz is on a distinguished road
Default

formula will work you just need to add the file path
Attached Files
File Type: xls Data_Validation%281%29(2).xls (22.5 KB, 10 views)
Reply With Quote
  #7  
Old 10-10-2012, 07:18 PM
grizz grizz is offline How to "hard link" two adjacent cells to a data validation drop down list? Windows XP How to "hard link" two adjacent cells to a data validation drop down list? Office 2003
Novice
 
Join Date: Jan 2012
Posts: 28
grizz is on a distinguished road
Default

If you notice if your drop down is blank you will get a #N/A displayed
C & D if you do not want that to appear check out the modified work sheet
Attached Files
File Type: xls Copy of Data_Validation%25281%2529%282%29(3).xls (24.5 KB, 5 views)
Reply With Quote
  #8  
Old 10-11-2012, 12:01 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How to "hard link" two adjacent cells to a data validation drop down list? Windows 7 64bit How to "hard link" two adjacent cells to a data validation drop down list? Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,484
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

I took the liberty of adding an array solution
Attached Files
File Type: xls Data_Validation_Array.xls (38.5 KB, 10 views)
Reply With Quote
  #9  
Old 10-12-2012, 04:17 AM
Geza59 Geza59 is offline How to "hard link" two adjacent cells to a data validation drop down list? Windows XP How to "hard link" two adjacent cells to a data validation drop down list? Office 2003
Novice
How to "hard link" two adjacent cells to a data validation drop down list?
 
Join Date: May 2012
Location: Budapest, Hungary
Posts: 20
Geza59 is on a distinguished road
Default

grizz:

Thank you for the modifications! Where I made the mistake was, that when I created a list, I highlighted the whole table instead of just the first columns.

You also helped me with the "#N/A" annoyance in unused cells - without asking for it -, indeed it made the whole page look full of errors, I just thought, I'll have to live with it.

Speaking of annoyances, there are two more, that it would be nice to correct.

One is that if I enter a word in the 1st column that doesn't exist in the look up table , I get ### in the 2nd column and #N/A in the 3rd one. How can I change the code, so that it will ignore such words in the 1st column and as a result leave the 2nd and 3rd column cells empty?

The other annoyance is that some of the items in the 1st column of the look up table does not have any data in its corresponding 2nd and 3rd column, as a result if I choose this item on the spreadsheet, the result is a zero in the 2nd and 3rd column of the spreadsheet. It would be nice to have the code not adding zeros to these columns in such instances.

I also have question about the IF condition of the code, that I couldn't found an answer on by searching through Google:

=IF($D97="";"";INDEX(...

What is the meaning of the expression of semicolon between double quotes?

My guess is, that it means "If $D97 equals to any item of the list (or anything except nothing)

...so how does one come up with the underlined code?

Pecoflyer:

Thank you for the array version, it works. Would you please, explain to me what is the advantage of doing this Excel coding with an array? (So, that I will know which one to use in the future.)
Reply With Quote
  #10  
Old 10-19-2012, 04:40 AM
Geza59 Geza59 is offline How to "hard link" two adjacent cells to a data validation drop down list? Windows XP How to "hard link" two adjacent cells to a data validation drop down list? Office 2003
Novice
How to "hard link" two adjacent cells to a data validation drop down list?
 
Join Date: May 2012
Location: Budapest, Hungary
Posts: 20
Geza59 is on a distinguished road
Default

grizz and Pecoflyer,

Thank you for your pointers and solution!
Reply With Quote
  #11  
Old 10-19-2012, 11:56 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How to "hard link" two adjacent cells to a data validation drop down list? Windows 7 64bit How to "hard link" two adjacent cells to a data validation drop down list? Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,484
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Quote:
Originally Posted by Geza59 View Post
Pecoflyer:

Thank you for the array version, it works. Would you please, explain to me what is the advantage of doing this Excel coding with an array? (So, that I will know which one to use in the future.)
Perhaps this link will give you some insight on what array formulas can do.
They do tend to slow things down, though
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
'Linking' entered information to other "cells" from an original "cell" in MS Word Wade Word 6 09-03-2012 05:22 PM
How to "hard link" two adjacent cells to a data validation drop down list? Data Validation drop down rkalapura Excel 1 05-27-2012 02:23 AM
How to "hard link" two adjacent cells to a data validation drop down list? How to choose a "List" for certain "Heading" from "Modify" tool? Jamal NUMAN Word 2 07-03-2011 03:11 AM
How to "hard link" two adjacent cells to a data validation drop down list? How the "Style" and the "List" are linked?? Jamal NUMAN Word 1 06-30-2011 05:18 PM
How to "hard link" two adjacent cells to a data validation drop down list? Conditional data validation (list drop-down) click4akshay Excel 2 04-28-2011 01:51 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:00 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft