Drop-list formulas?


New Member
Jul 24, 2010
First, great site. Still VERY new to using XL, most of the posts on this site are way over my head. Fair warning.
Using XL 2007 with XP OS.
I am unable to use xl genie for multiple reasons.
I currently have a 90 row 7 column user interface worksheet (used to fill multiple formats with the same info).
The user inputs an item number into ‘B2’ (first cell in the first row/column). Column A is blank and used just for spacing/looks, while row 1 has headers to inform the user what goes were.
In ‘C2’ is a drop-down list displaying ‘stock numbers’ dependent on ‘B2’. The following formula is used to populate the list and is located at M2:AL90.
{=IF(COLUMNS('Body Fill'!$B4:B4)<=COUNTIF(Dimensions!$A$2:$A$767,'Body Fill'!$B4),INDEX(Dimensions!$B$2:$B$767,SMALL(IF(Dimensions!$A$2:$A$767='Body Fill'!$B4,ROW(Dimensions!$A$2:$A$767)-ROW(Dimensions!$A$2)+1),COLUMNS('Body Fill'!$B4:B4))),"")}
D2 and E2 each contain drop-down lists (name and serial#) using the same formula modified to return the desired information. Range for ‘name’ is AM2:AY90; for ‘serial#’ AZ2:BY90 (now duplicated and modified to produce the same output on a separate worksheet). This enables the user to input 88 rows of validated data (180 drop-down lists, 3 per row; row 24 is hidden due to a mistake I made on the output format sheets).
The data base ‘Dimensions’ has multiple identical listings in each column except ‘serial#’ (user interface based on serial# is complete, Item number lookup is the last option I’m polishing up).
So, here’s the questions:
1. Is there a way to modify the above formula to ignore duplicate returns and blank/zeros, or possibly enter a formula in the data validation drop list to do so?
Example: item number ‘3’ is entered in cell ‘b2’, the data base returns the stock number of all ‘3’ items, stock number ‘20’ displays 15 times and stock number ‘21’ displays 10 times. Basically, I may have 40+ returns with only 5-10 unique values.
2. Does anyone know of a way to make the columns and rows swap places, without the formulas changing? If so, I located a solution to my issue on this site. Unfortunately, it seems the above formula needs the columns to expand in range to return the multiple hits under each lookup value. Manually changing the range for each cell would result in an overwhelming PITA:banghead: Figured it was time to ask for help before I shot my computer.
I would like for the user to be able to just insert or delete either a row or column to expand or contract the return/lookup range as the data base grows or shrinks (would still have to drag the formulas), not going to lose any sleep over it though.
ANY ideas, thoughts, suggestions, and/or help are welcome.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December


New Member
Jul 24, 2010
Sorry for the delay.

The data sheet is basicly as follows:

----Col a------Col b-------Col c
--'item num'-- 'stock#'----'Name'

the user sheet is as follows:

--------Col b-------Col c-----Col d-------Col M-AL
(row1)-'item num'--'stock#'---'Name'------'stock# lookup formula'

The way the drop list works now: after entering the item number (1) the formula will return every item under it (11,11,11,12,12,12,13) to include non-returns/blanks. This is also true for the drop lists in columns 'd' and 'e'.

Instead of mutiple returns of identical results, I'm wondering if there is a way to modify the formula to return only a single result for each unique value(11,12,13). Or possibly enter a formula into the data validation drop list range area to eliminate the duplicate returns.

I have seen a soulution for this with the return data in columns, using helper columns to return unique values only. I can not get the formula to work when i switch from rows to columns without manully changing the ranges.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member
John Strickland

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...