How to return specific column values in a separate sheet

Hi,

Good Day! I've been wondering if there is a way in excel that can return specific value on a separate sheet.

Attached is my sample spreadsheet for easier reference, as you can see in Sheet 2, this is how my raw file looks like whenever I generate my report and paste it here.

Under Sheet 1, I need to summarize certain details such as the Job no, Name and Address.

As I want to auto populate the details in Sheet 1 as I paste the raw files in Sheet 2, I need a formula in sheet 1 cell C1 and cell E1.

For cell C1 - I need a formula that will capture all the job numbers under sheet 2 and will return it in Sheet 1 same way as how it looks now, consecutively and with no row spaces in between.

Note: The job numbers is not limited to five values only, it changed from time to time. Sometimes a have a list of 10 or more and the first cell with job number doesn't always fall on cell A25.

For cell E1 - If only the address is within the same row as the job number I know I can just use a vlook up formula, but since it falls under the client name, is there a way i can have it reference in my Sheet 1 too in a way that is aligned with cell C1 and D1?

I hope my explanation makes sense.

Thank you to anyone who would take the time to help me on this. God Bless.

Hi so the +6 is the indicator of the next value the formula is supposed to pull right? can't we like just changed the criteria in a way that the formula instead reads and only returns the NUMBER values? I tried it in my actual file but sometimes the spaces in between is not just 6, sometimes the job number value falls in the 5th or 7th line in between and so the formula is returning a text value instead such as, "Active", "Normal"

Sure. A helper column can do.

Wow, this works perfectly! Thank you so much Ali for taking the time to respond on my query. God Bless!