Creating table that conditionally retrieves data in another sheet

[zebra2]

So here's what I'm trying to achieve: I have a table with info like so:
excel1.png

And what I want to do is, on a separate worksheet, automatically grab all row names with "TEST" in them and certain corresponding properties to make a new table:

excel2.png

Which ideally has some newly calculated cells in-between such properties.

What is the best way for me to go about this?

[Pete_UK]

As this is your first post, you may not know that many contributors are not able to view .png files on this forum due to software incompatibilities with some browsers. It is better to attach an Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

[zebra2]

Thanks for the tip, I've attached the sample workbook here, with the desired outcome on the second sheet, given the first sheet table.

[Pete_UK]

Put this formula in I2 of Sheet1:

=IF(ISNUMBER(SEARCH("TEST",B2)),MAX(I$1:I1)+1,"-")

then copy down to beyond your data in order to accommodate more data being added - the hyphens indicate where the formula is active.

Then in A2 of the other sheet you can use this formula:

=IFERROR(INDEX(Sheet1!$A:$G,MATCH(ROWS($1:1),Sheet1!$I:$I,0),MATCH(A$1,Sheet1!$A$1:$G$1,0)),"")

You can copy this across (omitting column D), and then copy those formulae down as far as you think you may need them.

Hope this helps.

Pete

[zebra2]

Thank you for the help. I can see the function of the first formula, but the second isn't generating any output for me. Are all the cells it refers to meant to be from Sheet1?

Edit: I changed the reference A$1 to sheet1 and now the program works as anticipated.

Excellent!

[Pete_UK]

Yes, you need to ensure that the headings in row 1 of Sheet2 are exactly the same as in Sheet1 (which they were in your sample file). Sorry, I forgot to point that out.

Glad to hear that you got it working.

Pete