How to return specific column values in a separate sheet

[MyStix01]

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.

[FDibbins]

IF your format for your raw data is always the same as your sample, this formula will pull the Job No...
=IF(ROWS($9:9)>COUNT(Sheet2!$A:$A),"",INDEX(Sheet2!$A:$A,19+6*ROWS($A$1:A1)))

This assumes that...
1. your data starts on row 25 - if not, adjust the ,19 part accordingly (19+6=25)
2. Your job numbers are actually real numbers, and not text (and not numbers that look like text)

To get the rest of the data, adjust the bolded part to match the column you want

[MyStix01]

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"

[FDibbins]

If you are willing to use a helper column, we can do that

[MyStix01]

Sure. A helper column can do.

[AliGW]

I think we can do it without a helper column. In C9:

=IFERROR(INDEX(Sheet2!A:A,SMALL(IF(ISNUMBER(Sheet2!$A$25:$A$52),ROW(Sheet2!$A$25:$A$52)),ROWS($A$1:A1))),"")

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then you just need to lookup the corresponding data. In D9:

=INDEX(Sheet2!$B$25:$B$52,MATCH(Sheet1!C9,Sheet2!$A$25:$A$52,0))

and in E9:

=INDEX(OFFSET(Sheet2!$B$25:$B$52,1,),MATCH(Sheet1!C9,Sheet2!$A$25:$A$52,0))

[MyStix01]

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

[AliGW]

You're welcome!