Vlookup to create report

[bmpreston]

Looking to pull a range of a VLOOKUP if a value exists.

Pull this to a dynamic report from multiple sheets onto one consolidated sheet.

Please see the example:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]value to match[/TD]
[/TR]
[TR]
[TD]foo[/TD]
[TD]foo[/TD]
[TD]foo[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]bar[/TD]
[TD]bar[/TD]
[TD]bar[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]foobar1[/TD]
[TD]foobar1[/TD]
[TD]foobar1[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD]helloworld[/TD]
[TD]helloworld[/TD]
[TD]helloworld[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

John Snow <--worksheet name

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]value to match[/TD]
[/TR]
[TR]
[TD]foobar1[/TD]
[TD]foobar1[/TD]
[TD]foobar1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]anothervalue[/TD]
[TD]anothervalue[/TD]
[TD]anothervalue[/TD]
[TD]-4[/TD]
[/TR]
[TR]
[TD]something[/TD]
[TD]something[/TD]
[TD]something[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]more[/TD]
[TD]more[/TD]
[TD]more[/TD]
[TD]-2[/TD]
[/TR]
</tbody>[/TABLE]

Elon Musk <--worksheet name


This is two example sheets of 12 in a workbook.

I need to put the name of the worksheet in a cell; add a few rows for gaps; then bring over the rows with a negative number; few rows, then the new worksheet name; then that sheet's negative values.


The Results would look like this:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]John Snow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]foo[/TD]
[TD]foo[/TD]
[TD]foo[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]foobar1[/TD]
[TD]foobar1[/TD]
[TD]foobar1[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Elon Musk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]anothervalue[/TD]
[TD]anothervalue[/TD]
[TD]anothervalue[/TD]
[TD]-4[/TD]
[/TR]
[TR]
[TD]more[/TD]
[TD]more[/TD]
[TD]more[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Thanks!