A special Offset/Lookup combination

[brockk]

Hi to all in the forum.

I am tring to do an inventory template for the materials and supplies of the office. In my workbook I have 2 worksheets (Template! & Inventory!) In the Template worksheet I have a number of lookups that need to be done to fill all the necessary fields of the Template worksheet in order to be able to print it.

Here's the scenario:

Inventory worksheet:

Has info. from columns A through G. (Item #, Description, Date, Reference, Recv'd, Used & Qty, respectively) The actual data begins in row 2 all the way down to 1400+. The breakdown in format of how the info. will be present simultaneously for each item # is the following format:

Item # 1's format:

1st row of data (ej. row 2) has Item # (Col. A, formated as Text) & Description (Col. B)
2nd row of data (ej. row 3) has Headers: Date, Reference, Recv'd, Used, & Qty. (Col. C through G)
3rd row of data (ej. row 4) has the actual data and it consists always of 15 rows. (ej rows 3 through 17)

Item # 2's format:
Same as first item only in rows 18 through 33.....and so forth there on.


Template Worksheet:

What I am trying to attempt is, in the Template worksheet, I will be entering in cell $K$5, the item # that I want displaying its inventory info. The actual data that I want to show up in Template!A18:E31 is that which pertains to the item # entered in $k$5. (Meaning: it will lookup in the Inventory worksheet and search if the entered # is valid, if its a valid #, I need a formula that can make it display the 5 columns and 15 rows of data for that particular Item #). Hope this is clear enough to understand.

I'm pretty sure that my response should be a Offset/Lookup combination of some sort but really can't get the actual hang of combining these two.

Anyways, thank you for any help that can be provided to solve this issue.

 

[jbeaucaire]

Try putting this formula in A18 on Template and copy it to the whole range A18:E31

=INDEX(Inventory!C:C,MATCH($K$5,Inventory!$A:$A,0)+ROW()-17)

 

[brockk]

Wow thanks jbeaucaire, for your reply...I've been trying to knock this one out for about a week now and just totally gave up. I managed to tweak it so it can print from the 3rd row of data, being that the rows headers are already included in the template. So I adjusted it from your formula: =INDEX(Inventory!C:C,MATCH($K$5,Inventory!$A:$A,0)+ROW()-17) to this one: =INDEX(Inventory!C:C,MATCH($K$5,Inventory!$A:$A,0)+ROW()-16).

Works beautifully, but I wanted to know if, the item wasn't filled with complete data "meaning: all 15 rows of data and for the sake of an example, there were only 7 or 8 rows of data, I would like to know if it is possible that when I am copying it to the Template worksheet if instead of filling these with 0, if these could be filled with blanks ("") instead. I would truly appreciate it if this could be capable within the formula.

Using the example of 8 rows of the 15 filled with data, the outcome should be:

rows 1 thru 8 data as is:
rows 9 thru 15 should be in blank.

Thank you once again for your help.

 

[jbeaucaire]

Click in EDIT > TOOLS > VIEW > [ ] Zero Values and uncheck that. That setting applies to this sheet only.

 

[brockk]

Thanks for the reply. I guess I forgot to mention the fact that I am using 2007. But did manage to find the option. Thank you kindly. You saved me on this one. Much grats.