Create a pivot from multiple consolidation ranges with more than one row?

Great tips on Pivot Tables can be found on Contextures.com, and about multiple consolidation ranges on this page specifically. Quoting from that page:

If possible, move your data to a single worksheet, or store it in a database, such as Microsoft Access, and you'll have more flexibility in creating the pivot table.

These days, you have other options in Excel. If you have Excel 2010 or higher you can use Power Query to combine data from different sheets, workbooks, databases, web, whatever, into a single data source that you can then use as the pivot table source data.

Power Query is a free add-in from Microsoft and runs with Excel Professional Plus. The business grade editions of Excel 2016 have it built into the Data ribbon under Get & Transform.