Display information from one worksheet to a Table of Contents...

[M15tyw00d]

I have multiple worksheets within a workbook.
I have a table of contents of sorts on a worksheet within the workbook with the name of each worksheet linked to that worksheet. I have four sets of columns. Sets are Column A and B, Column D and E, Column G and H and Column J and K. How can I do that? Also the table of contents continues to grow not in sets of columns but down the column as more totes are added. Would it auto add those cells? or would I need to change the range in the code?

N Totes	Location		N Drums	Location		H Tote	Location		C Tote	Location
NT #100404P			ND #0			H2900			42928-12
NT #42928-12			ND #1			H4621			0601329216

<tbody>
</tbody>

​

In the Location column I would like it to look to the worksheet linked, find the last row with text look in, Back at KWA first (if text then display KWA), Ship to KKA second (if text then display KKA), CRR # third (if text then display KWA-Dirty), and Ship Location 4th (if text then display text in the cell), and display it on the table of contents page in the Location cell. Also if CRR is the one that gets displayed; can it look in the column 30 day test and if there is an X could it display KWA-Dirty/30DT?

This is an example of the linked page to the Tote

Product

PO #

INV #

B/T #

Ship Location

Ship Date

Return Date

CRR #

Entered

30 day Test

Ship to KKA

Date

PO #

Back at KWA

Date

PO #

05234

1

1A

5673258

Company 1

1/13/2016

2/4/2016

551518

X

X

2/10/2016

6584321

X

2/25/2016

4561238

05234

2

2A

2316859

Company 2

3/2/2016

3/11/2016

551527

X

X

X

3/23/2016

5813942

05234

3

3A

2359413

Company 3

4/4/2016

<tbody>
</tbody>

 

[M15tyw00d]

Can anyone help with how to do this