Drop-list formulas?

[thomd71]

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 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-comfficeffice" /><o> </o>
<o> </o>
<o> </o>

 

[Aladin Akyurek]

Would you post a small anonymized sample along with the desired results?

 

[thomd71]

Sorry for the delay.

The data sheet is basicly as follows:

----Col a------Col b-------Col c
--'item num'-- 'stock#'----'Name'
-----1---------11----------a
-----1---------11----------b
-----1---------11----------c
-----1---------12----------a
-----1---------12----------b
-----1---------13----------c
-----2---------14----------a
-----2---------14----------b
-----2---------14----------c
-----2---------15----------a
-----2---------15----------b
-----2---------16----------c


the user sheet is as follows:

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

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.