complicate chemical inventory drop down reference.

**russader** · 02-17-2013, 01:48 PM

I am stuck with an odd one.
I'm running excel 2010.
I have all the chemicals in my warehouse in column A A2:A120.
I have each chemicals corrisponding LOT #s in D column D2:D120.
If we are temporarily out of a chemical we obviously don't have a lot #.
Lot #s are a series of alpha numerics.
I'm trying to make a seperate list starting at A121 which is a repeat of all chemicals listed above which have lot #s associated with them.

So if A2 is CaCl2 and we have it in hand D2 will have the Lot filled out and A121 will also say CaCl2.

A2 may say Calcium hydroxide but we are out so we delete it's lot. A3 may say Liquid nitrogen which we have on hand and so it does have a lot # in D3.

So A121 says CaCl2 and A122 says liquid nitrogen.

Any help would be appreciated.

**ChemistB** · 02-17-2013, 01:56 PM

You need to use an Array formula (use CNTRL SHFT Enter instead of ENTER. You'll see brackets {} appear around your formula)

In A121
=INDEX($A$2:$A$120,SMALL(IF(LEN($D$2:$D$121)>0,ROW($D$2:$D$121)-1,100000),ROW(A1)))
copied down
Questions?

**russader** · 02-17-2013, 05:51 PM

Delete see below

**russader** · 02-17-2013, 07:09 PM

Well I'm stuck out of town now for a few days. I only have an old laptop now with excel 2003. Will this be backwards compatible? If not Why and what would it take to make it so?

Edit: Trying it in 2003 seems to make A121=A2 but none of the othe cells below it do anything.

If I remove the lot # in D2... A121 does jump to = A5, the next chemical on hand, but still nothing below that.

**ChemistB** · 02-17-2013, 08:30 PM

It is backward compatable. Are you entering the formula using CNTRL SHFT ENTER?

**russader** · 02-18-2013, 12:20 PM

Every time I try it I get the same result. A121=A5.
A5 is the first chemical on hand.
A9 is the next.6
The longest spacing between chemicals on hand (those with lot #s) is 12.
Only A121 registers a chemical. And yes, I did hit ctrl+shift+enter.