Extracting Data From Multiple Sheets into 1 Sheet

[feliciag]

I'm working with multiple worksheets in one workbook and want to find a way to extract data from the multiple tabs into one sheet. Each tab has different headers, see below. I only want to pull out certain data. Each worksheet would be updated weekly.

TAB 1 HEADERS:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Shoot Date[/TD]
[TD]Air Date[/TD]
[TD]Release Date[/TD]
[TD]PROJECT[/TD]
[TD]STATUS[/TD]
[TD]CHANNEL[/TD]
[TD]STUDIO[/TD]
[TD]IM POINT [/TD]
[TD]CD[/TD]
[TD]WRITER [/TD]
[TD]PM[/TD]
[TD]DIRECTOR[/TD]
[TD]EDITOR[/TD]
[TD]BUDGET[/TD]
[/TR]
</tbody>[/TABLE]

TAB 2 HEADERS:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Air Date[/TD]
[TD]Project[/TD]
[TD]Budget[/TD]
[TD]Shoot Date[/TD]
[TD]STATUS[/TD]
[TD]PROMO TEAM[/TD]
[TD]SCHEDULE[/TD]
[TD]CREATIVE[/TD]
[TD]ISCI[/TD]
[/TR]
</tbody>[/TABLE]

TAB 3 HEADERS:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Air Date[/TD]
[TD]Project[/TD]
[TD]Budget[/TD]
[TD]Shoot Date[/TD]
[TD]STATUS[/TD]
[TD]PROMO TEAM[/TD]
[TD]SCHEDULE[/TD]
[TD]CREATIVE[/TD]
[TD]ISCI[/TD]
[/TR]
</tbody>[/TABLE]

TAB 4 HEADERS:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Air Date[/TD]
[TD]Project[/TD]
[TD]Budget[/TD]
[TD]Shoot Date[/TD]
[TD]

STATUS

<tbody>
</tbody>

[/TD]
[TD]PROMO TEAM[/TD]
[TD]PRODUCTION[/TD]
[TD]SCHEDULE[/TD]
[TD]CREATIVE[/TD]
[TD]ISCI[/TD]
[/TR]
</tbody>[/TABLE]

I only want the below info in the new sheet.
NEW SHEET HEADERS:
[TABLE="width: 500"]
<tbody>[TR]
[TD]PROJECT[/TD]
[TD]NETWORK[/TD]
[TD]PROMO TEAM[/TD]
[TD]AIR DATE[/TD]
[TD]SHOOT DATE[/TD]
[TD]IM CONTACT[/TD]
[TD]STATUS[/TD]
[TD]CREATIVE [/TD]
[/TR]
</tbody>[/TABLE]

I have excel 2016. I'm working off of a mac. I don't seem to have Power Query and am restricted from downloading in my office.

Please let me know if you need more info.
Thank you,
Felicia

 

[pbornemeier]

Is the PROJECT value the common link between the four tabs?
Most of the elements you want to include in the new sheet can be taken from more than one input tab. What is order of precedence for data that differs between tabs?
Is Network Channel or Studio? (don't know)
Does IM Contace = IM Point (probably)

The table below looks odd, but when pasted into a workbooks straightens out.
NEW SHEET: PROJECT NETWORK PROMO TEAM AIR DATE SHOOT DATE IM CONTACT STATUS CREATIVE
TAB 1 HEADERS: PROJECT "CHANNEL ??
STUDIO ??" Air Date Shoot Date IM POINT ?? STATUS
TAB 2 HEADERS: Project PROMO TEAM Air Date Shoot Date STATUS CREATIVE
TAB 3 HEADERS: Project PROMO TEAM Air Date Shoot Date STATUS CREATIVE
TAB 4 HEADERS: Project PROMO TEAM Air Date Shoot Date STATUS CREATIVE