Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Vlookup column drop down for each countif

Posted on 2013-11-15
5
Medium Priority
?
449 Views
Last Modified: 2013-11-16
I don't even know how to ask this.  I have a spreadsheet with values on it that link to pictures.  There are more or less 3 pictures for each value.  what I want to do is create a drop down so the user can pick a photo number based on the count if.  essentially I want the user to be able to see each photo of the values.
Inventory.xlsx
0
Comment
Question by:Jenedge73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 

Author Comment

by:Jenedge73
ID: 39652841
Ok you guys are a hell of a lot smarter than me.  I just don't know how to ask it.  Please see the yellow cells in the file below.  all the hyperlink formulas link now I just need to see the other links as well.
Thanks
Inventory1.xlsx
0
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 39653254
Hi Jenedge73,
Still not quite sure what you are needing here, and there probably is a way to do it, but you are trying to make excel do something it isn't so good at. It is like using a nail file to unscrew a wall plate. You may want to consider graduating up to a database program like Microsoft Access to handle this.

That being said, tell me what your expected outcome is: give me a real life scenario

kfalandays
0
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 39653271
Hello again,
 I think I see what you are wanting to do, and it can be accomplished with data validation. To find the exact steps to do this, search the web for "dependent drop down lists" and you will find a wide selection of resources, including video tutorials

Hope that helps
kfalandays
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 39653319
Jenedge73,
I think what you are wanting is:
1. Find the first cell in Locations worksheet column A that contains the value in cell A15
2. Create a named range that returns values from Locations column B starting in that same row, and continuing for as many column A matches as exist.
3. Use that named range as the Source in a data validation dropdown

In the particular example of cell A15, you want the dropdown to display the following three values:
Global Tubing Red Deer Yard\CPM-1011-648,String3006,Stock.jpg
Global Tubing Red Deer Yard\CPM-1011-648,String3006,Stock_Pic.jpg
Global Tubing Red Deer Yard\CPM-1011-648.jpg

To approach this problem, I did the following:
1.  Select cell B15
2.  In the Formulas...Name Manager menu item, click the New... button
3.  Enter ddPictures for the Name
4.  Use the following Refers to  formula:
=INDEX(Locations!$B$1:$B$1000,MATCH('Inventory List'!$A15,Locations!$A$1:$A$1000,0)):INDEX(Locations!$B$1:$B$1000,MATCH('Inventory List'!$A15,Locations!$A$1:$A$1000,0)+COUNTIF(Locations!$A$1:$A$1000,'Inventory List'!$A15)-1)
5.  In the Data...Validation menu item, choose List as the type and enter the following as Source:
=ddPictures
6.  Copy cell B15
7.  Select the cells that need dropdowns in column B
8.  Use the Paste Special...Validations menu item to copy the validations everywhere required

The selected cell in step 1 and the formula in step 4 both refer to row 15. Because the named range formula contains a relative row number (cell $A15), it will automatically adjust to the correct row number when you use ddPictures in different rows.

Brad
InventoryQ28295540x.xlsx
0
 

Author Closing Comment

by:Jenedge73
ID: 39653384
WOW! Thanks guys.  Like I said im sorry I wasn't more clear.  you guys are amazing
thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question