+ Reply to Thread
Results 1 to 3 of 3

look for value on a worksheet and report a list

  1. #1
    Registered User
    Join Date
    09-20-2012
    Location
    Reading, UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    look for value on a worksheet and report a list

    Hello all,

    I am looking for some help.

    I have a list of equipment and bits that go with each kit and depending on other kits which they are installed with.
    I'd like to automate the list.

    Attached an example: I'd like excel to look for the first piece of equipment over their respective sheets and list the parts needed for the chosen equipment.

    Could you point me in the right direction? I'll keep looking in the meantime!

    Thanks for your help
    Tony
    Attached Files Attached Files

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: look for value on a worksheet and report a list

    Put in C5 and copied cross (Array FOrmula):

    =IFERROR(IF($B$4="equipment 1";INDEX('Equipment 1'!$B$4:$D$5;SMALL(IF(('Equipment 1'!$A$4:$A$5=$B5)*('Equipment 1'!$B$4:$D$5<>"");MATCH(ROW('Equipment 1'!$B$4:$D$5);ROW('Equipment 1'!$B$4:$D$5));"");COLUMN(A1));1*(SMALL(IF(('Equipment 1'!$A$4:$A$5=$B5)*('Equipment 1'!$B$4:$D$5<>"");MATCH(ROW('Equipment 1'!$B$4:$D$5);ROW('Equipment 1'!$B$4:$D$5))+1*MATCH(COLUMN('Equipment 1'!$B$4:$D$5);COLUMN('Equipment 1'!$B$4:$D$5));"");COLUMN(A1))-SMALL(IF(('Equipment 1'!$A$4:$A$5=$B5)*('Equipment 1'!$B$4:$D$5<>"");MATCH(ROW('Equipment 1'!$B$4:$D$5);ROW('Equipment 1'!$B$4:$D$5));"");COLUMN(A1))));IF($B$4="equipment 2";INDEX('Equipment 2'!$B$4:$D$5;SMALL(IF(('Equipment 2'!$A$4:$A$5=$B5)*('Equipment 2'!$B$4:$D$5<>"");MATCH(ROW('Equipment 2'!$B$4:$D$5);ROW('Equipment 2'!$B$4:$D$5));"");COLUMN(A1));1*(SMALL(IF(('Equipment 2'!$A$4:$A$5=$B5)*('Equipment 2'!$B$4:$D$5<>"");MATCH(ROW('Equipment 2'!$B$4:$D$5);ROW('Equipment 2'!$B$4:$D$5))+1*MATCH(COLUMN('Equipment 2'!$B$4:$D$5);COLUMN('Equipment 2'!$B$4:$D$5));"");COLUMN(A1))-SMALL(IF(('Equipment 2'!$A$4:$A$5=$B5)*('Equipment 2'!$B$4:$D$5<>"");MATCH(ROW('Equipment 2'!$B$4:$D$5);ROW('Equipment 2'!$B$4:$D$5));"");COLUMN(A1))))));"")
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-20-2012
    Location
    Reading, UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: look for value on a worksheet and report a list

    Hello Azumi,

    Thanks for your idea.
    The only downside is the length of the formula: I will have one tab for each type of equipment and that may be a problem.

    I believe I've come up with an alternative. After all I know the name of the tab (=the name of the equipment) and then it becomes just a simple VLOOKUP task.

    Please Login or Register  to view this content.
    Where B3 is the first parameter and B2 the second. I just need to make sure the tabs have the proper names.

    Thanks again for your time!
    Tony

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Suggestion-[Report Post] Drop Down List to Pick And Report The Type of The Post
    By :) Sixthsense :) in forum Suggestions for Improvement
    Replies: 3
    Last Post: 12-16-2013, 08:37 AM
  2. Replies: 4
    Last Post: 03-25-2013, 12:58 PM
  3. Replies: 0
    Last Post: 07-09-2012, 03:10 PM
  4. Replies: 1
    Last Post: 12-20-2005, 12:20 PM
  5. Report changes to other worksheet
    By Martie in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-04-2005, 12:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1