Excel 2003: Display results of Inventory.

[Lensmeister]

Morning all,

I have a sheet named data which has the columns

Item (i.e. Trousers, Jackets, Boilersuits etc.)
Size (i.e. 30 / Reg, 32 / Reg etc.)
In Stock (9, 4 , etc.)

This isn't all the columns but they are the ones I need to reflect in the Switchboard/Report page.

The Switchboard page has a drop down box where items can be chosen (i.e. Trousers Jackets etc.)

While I an use Vlookup to populate the FIRST row, I cannot work out how to get the rest of the sizes to show in the results table.


I have attached a copy of hte spreadsheet.

Thanks for the help all

[:) Sixthsense :)]

In B5 Cell of Switchboard Sheet - Array Formula - Requires CTRL+SHIFT+ENTER

=IFERROR(INDEX(data!$B$2:$B$67,SMALL(IF(data!$A$2:$A$67=$A5,ROW(data!$A$2:$A$67)-ROW(data!$A$2)+1),ROW($A1))),"")

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Drag it down...

[Lensmeister]

Thanks Sixthsense

I have copied the formula, pressed CTL+V to paste it in then pressed the CTL+Shift and then pressed Enter alas it resultred in a #Name? Error

What did I do wrong?

[:) Sixthsense :)]

Oops.. sorry for it since I have not seen your excel version. Iferror() is the new arrival in excel 2007.

Try the below one...

INDEX(data!$B$2:$B$67,SMALL(IF(data!$A$2:$A$67=$A5,ROW(data!$A$2:$A$67)-ROW(data!$A$2)+1),ROW($A1)))

Yes... It is an array formula so requires CTRL+SHIFT+ENTER

[:) Sixthsense :)]

If you want to add the error handler then try the below one...

In B5 Cell of Switchboard Sheet - Error Handler Included - Array Formula - Requires CTRL+SHIFT+ENTER

Please Login or Register  to view this content.

[Lensmeister]

EXCELLENT ... Thank you so much

Will now apply this to our items ... Maybe I'll extend it to our Safety boots items as well

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback and rep

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.