Aggregate and Vlookup formula problems

[wheath]

I have a spreadsheet where I enter codes that labor hours will be charged to. I also have a spreadsheet where I would like the codes to transfer over to. I have formulas in that are working ... somewhat. It isn't capturing all the codes that have labor.

This is the spreadsheet I input the information into:

Excel 2010
	C
63	20
Job Cost

This is the spreadsheet I would like the information to transfer into:

Excel 2010
	Q
28	02272
Field Time Sheet

Cell Formulas
Range	Formula
Q28	=IF(ROWS($Q$14:Q28)>$T$6,"",INDEX('Job Cost'!$A$7:$A$70,AGGREGATE(15,6,ROW('Job Cost'!$A$7:$A$70)-ROW('Job Cost'!$A$7)+1/('Job Cost'!$C$7:$C$70>0),ROWS($Q$14:Q28))))

I hope you can see that it isn't grabbing everything:

Excel 2010
	Q
28	02272
Field Time Sheet

Cell Formulas
Range	Formula
Q28	=IF(ROWS($Q$14:Q28)>$T$6,"",INDEX('Job Cost'!$A$7:$A$70,AGGREGATE(15,6,ROW('Job Cost'!$A$7:$A$70)-ROW('Job Cost'!$A$7)+1/('Job Cost'!$C$7:$C$70>0),ROWS($Q$14:Q28))))

Here is the formula in T6
=COUNTIF('Job Cost'!$C$7:$C$70,">0")

Here is the formula in column Q
=IF(ROWS($Q$14:Q14)>$T$6,"",INDEX('Job Cost'!$A$7:$A$70,AGGREGATE(15,6,ROW('Job Cost'!$A$7:$A$70)-ROW('Job Cost'!$A$7)+1/('Job Cost'!$C$7:$C$70>0),ROWS($Q$14:Q14))))

Here is the formula in column R
=IF(Q14="","",VLOOKUP(Q14,'Job Cost'!$A$7:$B$70,2,0))

I think the formula in column Q is the problem.

Please help

 

[wheath]

Unfortunately the forum thinks my html is too large or something, so I would have to send the spreadsheet to someone.

 

[Aladin Akyurek]

Unfortunately the forum thinks my html is too large or something, so I would have to send the spreadsheet to someone.

Where do you enter the first instance of the formula you posted? And what is the formula of T6?

 

[wheath]

Where do you enter the first instance of the formula you posted? And what is the formula of T6?

This posting may be more helpful
http://www.mrexcel.com/forum/excel-questions/955518-vlookup-aggregate-formulas.html

 

[Peter_SSs]

Here is the formula in column Q
=IF(ROWS($Q$14:Q14)>$T$6,"",INDEX('Job Cost'!$A$7:$A$70,AGGREGATE(15,6,ROW('Job Cost'!$A$7:$A$70)-ROW('Job Cost'!$A$7)+1/('Job Cost'!$C$7:$C$70>0),ROWS($Q$14:Q14))))

A quick look would make me think that this formula is missing a set of parentheses.

=IF(ROWS($Q$14:Q14)>$T$6,"",INDEX('Job Cost'!$A$7:$A$70,AGGREGATE(15,6,(ROW('Job Cost'!$A$7:$A$70)-ROW('Job Cost'!$A$7)+1)/('Job Cost'!$C$7:$C$70>0),ROWS($Q$14:Q14))))

 

[wheath]

A quick look would make me think that this formula is missing a set of parentheses.

=IF(ROWS($Q$14:Q14)>$T$6,"",INDEX('Job Cost'!$A$7:$A$70,AGGREGATE(15,6,(ROW('Job Cost'!$A$7:$A$70)-ROW('Job Cost'!$A$7)+1)/('Job Cost'!$C$7:$C$70>0),ROWS($Q$14:Q14))))

Thank you Peter_SSs, but that wasn't it ... I just can't figure out why it is skipping rows

For whatever reason when I try to load the HTML, it crashes Mr. Excel.

Here is a link to the spreadsheet:
https://drive.google.com/file/d/0Bxl...ew?usp=sharing

It is the sheet titled "Job Cost" that I input the information. The place I want the information to populate is in "Field Timecard." If you look in Columns Q & R in "Field Timecards" you will see the formulas for pulling the information, however, you will also notice that it isn't pulling to the second page of the timesheet properly. Any suggestions?

 

[Peter_SSs]

I don't wish to download the file.

For whatever reason when I try to load the HTML, it crashes Mr. Excel.

Can you describe what happens?

 

[wheath]

It is the sheet titled "Job Cost" that I input the information. The place I want the information to populate is in "Field Timecard." If you look in Columns Q & R in "Field Timecards" you will see the formulas for pulling the information, however, you will also notice that it isn't pulling to the second page of the timesheet properly. Any suggestions?

The location the information is pulling to, skips over some of the lines in the Job Cost location. If you look at the second page of the Field Time Sheet, it skips to lines lower on the Job Cost, so its overlooking some lines it looks like.

 

[Peter_SSs]

If post #8 is a response to #7, then I think that you

a) Missed the first line in #7

b) Haven't understood that my request to explain was about the HTML maker 'crashing' MrExcel.

 

[wheath]

If post #8 is a response to #7, then I think that you

a) Missed the first line in #7

b) Haven't understood that my request to explain was about the HTML maker 'crashing' MrExcel.

Sorry, I didn't understand you were asking about what happens with Mr. Excel crashing
I get a pop-up that says it crashed and to kill or wait for the page, it has a sad face