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:oops: 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>

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.


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.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back