Solved

Vlookup column drop down for each countif

Posted on 2013-11-15
5
402 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
  • 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 17

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 17

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 80

Accepted Solution

by:
byundt earned 500 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now