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