Returning non blank cells fro multiple worksheets

[BWellman]

Hey,

I am looking to write a formula to return non blank cells from multiple worksheets. The slight complication is that I need to return the value that is in the non blank cell, and the value of the cell to the right.

In the attached document, I am looking to return the non blank values from the tables on sheet 1, sheet 2, and sheet 3 into the blue highlighted columns (G,H) on the final tab. As you can see, if the cell is non blank, I need the name and the segment to be returned.

If that is not possible, there is the option of using the table on the final tab which just pulls all of the values from the different worksheets into one table. From there, I still have the same problem of getting rid of the non blank cells from two columns

Cheers,
B

[63falcondude]

In G3, try

=IFERROR(INDEX(C$3:C$38,SMALL(IF($D$3:$D$38<>"",ROW($C$3:$C$38)-(ROW($C$3)-1)),ROWS($A$1:$A1))),"") Ctrl Shift Enter
Drag this over to H3 and then down as far as needed.

This does depend on the table to the left (which can be hidden if you choose).

[Estevaoba]

Check out this thread:

https://www.excelforum.com/excel-for...ter-sheet.html